Why for the other fast?

query_question.sql

select mt.id from massive_table mt
where not exists 
  (select id from big_table bt where bt.massive_table_id=mt.id);


select mt.id from massive_table mt
left join big_table bt on bt.massive_table_id = mt.id
where bt.massive_table_id is null;

-- The left join query appears to return results 100x faster.
-- Is the 2nd query that much more efficient than the 1st query or
-- is MySQL just caching the query better?

I actually think the 1st query isn't getting cached while the 2nd one is. Thoughts?

Looks like query 1 is using a correlated subquery, where the inner query is using a column from the outer query. So for every row, you make another query to see if rows exist in the big_table. The left join will always be faster than a correlated subquery.

👍 you rock spencer