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


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?

  • I don't think exists should take time like 79 second and join take 72 milliseconds..... Test once again may be you had lag – Deepak Kumar Nov 29 '18 at 3:15
  • Now I tried, it took 85.7 seconds. I've updated the total number of records for each table in the question. – Js Lim Nov 29 '18 at 3:22
  • Can you explain what you aim behind this ? If u need total number or just match once something in more detail – Deepak Kumar Dec 4 '18 at 15:13
  • And try to match table 2 to with table 1 as it has less entry may be there is some change in time but i am not really sure about this – Deepak Kumar Dec 4 '18 at 15:16
  • Want to get the total records of table_1, where the table_2 name must be John – Js Lim Dec 7 '18 at 2:51

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.

  • Thanks. Would you mind to provide an example query for both EXISTS & JOIN? (P/S: The example that I show is in our production DB) – Js Lim Nov 29 '18 at 3:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.