Slow "IN" Queries with Subqueries in MySQL

MySQL queries using the "IN" operator can exhibit significant performance degradation when the subquery used to retrieve the values for the "IN" clause is complex. In such cases, substituting the subquery results with explicit values results in significant improvements in execution time.

To understand the cause of this behavior, it's important to note that MySQL executes subqueries every time the "IN" query is evaluated. In the example provided, there are 7 million rows in the em_link_data table, each of which is processed separately, resulting in numerous subquery evaluations.

Replacing the subquery with explicit values, on the other hand, eliminates the need for repeated subquery executions, resulting in a substantial performance boost. A similar performance advantage can be achieved by using a JOIN instead of an "IN" subquery, further optimizing the execution process.

Unfortunately, the user is unable to modify the query due to software limitations. In such cases, it's worth investigating alternative methods to improve performance, such as optimizing the tables involved by creating appropriate indexes or exploring ways to reduce the number of rows processed by the query.