MySQL under what scenario suitable to use WHERE EXISTS 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?
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.