MySQL Question

Optimising a MySQL query I wrote

[Sekka]

Hi,

For the sake of this thread I have simplied my table structure and query so it's easier for everyone.

I am building a mailing list system in PHP and MySQL. I have a table called "addresses" where all the email addresses for all the mailing lists are kept. I also have another table called "blacklist" where all the unsubscribed emails are kept.

Now, I wanted to pull all email addresses from a certain mailing list in one query, but also leave out the addresses within the black list.

The query I am using is,

SELECT T1.* FROM addresses AS T1, blacklist AS T2 
WHERE T1.email <> T2.email

Now, I usually use INNER and LEFT joins and I have never "joined" two tables this was before.

My question is, does this add a lot more to the server load when the query is run? Is there anyway I can optimise this or is this the best way?

Thank you

[mcibor]

You can always run explain and see what is being performed by this query.

The other option would be:

SELECT * FROM addresses WHERE email NOT IN (SELECT email FROM blacklist)

regards
Michal

[Sekka]

Never heard of that, will it a go.

How do I interpret the EXPLAIN then?

[eelixduppy]

>> How do I interpret the EXPLAIN then?

Optimizing Queries with Explain [dev.mysql.com].

You may also want to take a peek at mysql optimization [dev.mysql.com]. It's a good resource--bookmark it :)

[edited by: eelixduppy at 3:03 pm (utc) on Mar. 16, 2007]

[Sekka]

Thank you very much.

I will deffo give it a read.

[mcibor]

Added to ctrl+d :)
Thanks Scott!