Showing posts with label mysql benchmark join. Show all posts
Showing posts with label mysql benchmark join. Show all posts

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