Why is `IN` slower than `=` in MySQL queries, even with a single value?

MySQL's IN vs. = Performance Discrepancy

The Issue: MySQL queries using the IN operator can be surprisingly slower than those using the = operator, even when IN compares against a single value.

Root Cause: The problem lies in how MySQL handles subqueries within IN clauses. Older versions (prior to 5.6) often misidentify these subqueries as dependent subqueries.

Dependent vs. Independent Subqueries: A dependent subquery is re-executed for each row in the outer query, leading to significant performance overhead. An independent subquery is executed only once. MySQL's earlier versions incorrectly treated IN subqueries as dependent, even when they returned a single value.

EXPLAIN Analysis: The EXPLAIN command reveals this behavior. An IN query with a subquery shows "DEPENDENT SUBQUERY" in the execution plan, while an equivalent query using = shows "SUBQUERY".

Example EXPLAIN output (showing the issue):

<code>1  'PRIMARY'             'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Using where'
2  'DEPENDENT SUBQUERY'  'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Using where'
3  'DEPENDENT SUBQUERY'  'question_law'          'ALL'  ''  ''  ''  ''  10040  'Using where'</code>

Example EXPLAIN output (after fixing the issue with =):

<code>1  'PRIMARY'   'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Using where'
2  'SUBQUERY'  'question_law_version'  'ALL'  ''  ''  ''  ''  10148  'Using where'
3  'SUBQUERY'  'question_law'          'ALL'  ''  ''  ''  ''  10040  'Using where'</code>

Illustrative Example:

This simple query demonstrates the problem:

<code class="language-sql">SELECT id
FROM foo
WHERE id IN (SELECT MAX(foo_id) FROM bar);</code>

Even with a single value returned by the subquery, it's treated as dependent, resulting in slow execution. Replacing IN with = dramatically improves performance.

Solution:

The performance issue was addressed in MySQL 5.6 and later versions. Upgrading to a newer version resolves this discrepancy. If upgrading isn't feasible, rewriting the query to avoid IN with a subquery (using joins, for example) can be a workaround.

The above is the detailed content of Why is `IN` slower than `=` in MySQL queries, even with a single value?. For more information, please follow other related articles on the PHP Chinese website!