Thursday, May 9, 2019

In For The Win!

Someone on stackoverflow observed that IN is faster than EXISTS on MySQL

select * from `replays` 
where id in (
    select replay_id from `players` 
    where `battletag_name` = 'test') 
order by `id` asc 
limit 100;

select * from `replays` 
where exists (
    select * from `players` 
    where `replays`.`id` = `players`.`replay_id` 
      and `battletag_name` = 'test') 
order by `id` asc 
limit 100;

EXISTS took 70 seconds. IN took 0.4 second.


Aside from IN being faster than EXISTS in most cases, readability of IN is a big win. And also with IN, queries can be made modular, e.g.,


with test_players as 
(
    select replay_id 
    from players 
    where battletag_name = 'test'
) 
select * 
from replays 
where id in (select replay_id from test_players)

Cannot do the above when using EXISTS.