Why is MySQL's `IN` operator slower than `=` even with a single value?

MySQL query performance exception: performance difference between IN operator and equal sign operator

Problem Description

In MySQL, the performance of a SELECT query using the IN condition is significantly lower than the same query using the equal sign (=) operator. Even if the IN condition contains only a single value, the performance difference can be significant.

Explanation

This issue stems from an optimization flaw in MySQL that has been fixed in MySQL 5.6.x. This problem occurs when a subquery containing an IN condition is incorrectly classified as a dependent subquery rather than an independent subquery.

Dependent subquery and independent subquery

• Dependent subquery: For each row in the outer query, the dependent subquery is executed once, resulting in multiple calculations.
• Independent subquery: is executed only once, regardless of the number of rows in the outer query.

Sample query analysis

The following example query demonstrates this anomaly:

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

When executing this query, the subquery is treated as a dependent subquery, resulting in poor performance. However, replacing IN with = removes the dependency and significantly improves performance.

Query plan comparison

Inspecting the query plan using the EXPLAIN command reveals the difference:

• IN Condition:
  • 'PRIMARY' 'question_law_version' 'ALL' '' '' '' '' 10148 'Using where'
  • 'DEPENDENT SUBQUERY' 'question_law_version' 'ALL' '' '' '' '' 10148 'Using where'
  • 'DEPENDENT SUBQUERY' 'question_law' 'ALL' '' '' '' '' 10040 'Using where'
• Equal sign condition:
  • 'PRIMARY' 'question_law_version' 'ALL' '' '' '' '' 10148 'Using where'
  • 'SUBQUERY' 'question_law_version' 'ALL' '' '' '' '' 10148 'Using where'
  • 'SUBQUERY' 'question_law' 'ALL' '' '' '' '' 10040 'Using where'

Please note that there is no "DEPENDENT SUBQUERY" in the query plan using the = operator.

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