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 Nov 29, 2018 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, 2018 at 3:22
  • Can you explain what you aim behind this ? If u need total number or just match once something in more detail Dec 4, 2018 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 Dec 4, 2018 at 15:16
  • Want to get the total records of table_1, where the table_2 name must be John
    – Js Lim
    Dec 7, 2018 at 2:51

1 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.

  • 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, 2018 at 3:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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