To decide whether to use 'NOT IN' or a left join, to find out all the rows in a table that did not have a relationship to another table, e.g. find all houses with no kittens.
I used the benchmark() mysql command to compare, in summary there was not much difference at all, using the mysql command prompt from localhost.
Statement 1
select benchmark(10000000000,'select * from houses where id not in (select house_id from kittens) ORDER BY id');
18.47s
18.51s
18.49s
18.52s
18.52s
Statement 2
select benchmark(10000000000,'select houses.* from houses left join kittens on houses.id=kittens.house_id where kittens.house_id is NULL ORDER BY houses.id');
18.51s
18.49s
18.50s
18.50s
18.51s
Friday, February 16, 2007
Subscribe to:
Post Comments (Atom)
3 comments:
looks like same
Bad benchmark. JOIN's are optimized for HUGE tables; not for repeated use.
heres a benchmark on huge tables , with explanation. Giving the same results.
http://explainextended.com/2009/09/18/not-in-vs-not-exists-vs-left-join-is-null-mysql/
Post a Comment