MySQL under what scenario suitable to use WHERE EXISTS

Question

I recently just noticed that WHERE EXISTS can slow down the operation badly. E.g.

select SQL_NO_CACHE count(*) as aggregate
from `table_1`
where exists (select *
              from `table_2`
              where `table_1`.`fk_id` = `table_2`.`id`
                and `name` = 'john');

79 seconds

VS

select SQL_NO_CACHE count(*) as aggregate
from `table_1`
inner join `table_2` on `table_1`.`fk_id` = `table_2`.`id`
where `table_2`.`name` = 'John';

72 milliseconds

• table_1 has total 4,723,710 records
• table_2 has total 1,236 records

Just wanted to know, since the performance of WHERE EXISTS so bad, under what scenario do we need to use this?

Answer 1

Not a fair test. Your example is about as close as you can get between using JOIN to using EXISTS.

Also, it probably gave a different result -- What if there are 2 Johns? The JOIN would count both; the EXISTS would count only once.

EXISTS is a "semi-join". It is better than JOIN in cases where it can stop short of scanning for all rows that match. Instead, it stops with the first match. If there is a useful index, semi-join does not necessarily help much. If not, it could be a big speed up.

In the past, IN ( SELECT ... ) was notoriously inefficient. EXISTS (SELECT * ) is often a better choice.

Search around stackoverflow.com for more instances of EXISTS. I think you will find a variety.