Why does using \'IN\' with a subquery slow down MySQL queries, but not when using explicit values?

Slow MySQL Queries with "IN" vs. Explicit Values

In MySQL, the "IN" operator can cause significant performance degradation when accompanied by a subquery, although it performs swiftly with explicit values.

Problem

Consider the following MySQL query:

SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
WHERE
linkid in (SELECT l.id FROM em_link l WHERE l.campaignid = '2900' AND l.link != 'open')

Despite the subquery and column indexing, this query takes approximately 18 seconds to execute. However, substituting the subquery results with explicit values significantly speeds up the query, completing in under 1 millisecond:

SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
WHERE
linkid in (24899,24900,24901,24902);

Reasoning

The performance disparity stems from the subquery's behavior. In MySQL, subqueries are evaluated every time they are referenced. Thus, the original query executes the subquery approximately 7 million times, resulting in slower performance.

Solution

To address this issue, consider using a JOIN instead of a subquery:

SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
INNER JOIN em_link l
ON em_link_data.linkid = l.id
WHERE
l.campaignid = '2900' AND l.link != 'open'

This approach executes a single query, significantly improving performance.

The above is the detailed content of Why does using \'IN\' with a subquery slow down MySQL queries, but not when using explicit values?. For more information, please follow other related articles on the PHP Chinese website!