EXISTS much faster then IN in MySQL

Today I was making some queries on a database with a lot of records.

The table I was making the query on had 25.000.000 records.

The performance of the query I’ve made was terrible, the structure was as follows (fake column names used):

SELECT * FROM t_posts
WHERE id IN
(SELECT id
FROM t_topics
WHERE id BETWEEN 150000 AND 150001)

This query took 12.5085 seconds to complete, for retrieving  just 3 records!

Then I’ve decided to take a look how long MySQL would take to execute the query, when I used EXISTS instead of IN.

The query had the following structure:

SELECT * FROM t_posts p
WHERE EXISTS
(SELECT t.id FROM t_topics t
WHERE id BETWEEN 150000 AND 150001
AND t.id = p.topicid)

This query only took 2.8636 seconds to complete!

This means that the performance of the query increasing with almost 10 seconds, and is almost 6 times as fast!

So when you are running into performance problems in MySQL with your query using the IN command, please consider to use the EXISTS method!

Usefull links:
Optimizing IN subqueries in MySQL

Documentation on the EXISTS syntax in MySQL

Documentation on how to use IN in subqueries in MySQL

No related posts.

Related posts brought to you by Yet Another Related Posts Plugin.