Chris Chris - 1 year ago 42
MySQL Question

MySQL most efficient method to find missing values from multiple tables

I have three tables, the first is a list of email addresses:

id - integer, this is the primary key<br>
email - varchar(255) field holding the address

sid - integer, foreign key references id in addresses table

rid - integer, foreign key references id in addresses table

Obviously the "sent" and "received" tables have other columns, but they are not important for this question. the sent and received tables are populated every time an email is sent or received and if the address is not already in the "addresses" table, it gets added. The tables can get quite large (100,000+).

Entries for the "sent" and "received" tables are purged on a regular basis and entries removed for various reasons, leaving orphaned entries in the "addresses" table.

I am looking for the most efficient method in MySQL to purge orphaned entries in the "addresses" table. The query I have so far is:

from addresses
where id not in
(select rid from received)
and id not in
(select sid from sent);

This works, but it can take a looong time to run and is definitely not the most efficient way of doing this! I also tried this:

from addresses
where not exists
(select 'x' from sent where
and not exists
(select 'x' from rceieved where;

This was a bit quicker, but still takes a long time, I suspect I need to use the JOIN syntax but my sql knowledge has run out on me at this point !

Answer Source

This should do the trick

DELETE adresses.* FROM adresses 
LEFT JOIN received ON
WHERE sent.sid IS NULL AND received.rid IS NULL