Friday, February 16, 2007

MySQL benchmark left join vs not in

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

3 comments:

Unknown said...

looks like same

Unknown said...

Bad benchmark. JOIN's are optimized for HUGE tables; not for repeated use.

kommradHomer said...

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/