Why is `IN` slower than `=` in MySQL subqueries?

The reason why the IN operator is slower than the = operator in MySQL

In some cases, IN conditions in MySQL execute slower than = conditions. This is a known issue. The root cause is that when the MySQL optimizer processes subqueries in IN conditions, it sometimes mistakenly identifies them as dependent subqueries instead of independent subqueries.

Thus, the subquery in the IN condition is executed once for each row in the parent query, while the subquery in the = condition is executed only once. This can lead to significant performance differences, especially when working with large data sets.

Let’s look at an example of a SELECT query:

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

Using EXPLAIN to analyze this query, the results might look like this:

<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>

The

"DEPENDENT SUBQUERY" line indicates that the subquery in the IN condition is being executed for each row of the parent query.

If we change the IN condition to =:

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

The output of

EXPLAIN will become:

<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>

The "SUBQUERY" line indicates that the subquery is now executed only once, resulting in faster execution.

This issue has been resolved in MySQL 5.6.x version. If possible, upgrading to a newer MySQL version may resolve this issue and gain performance improvements.

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