Home > Database > Mysql Tutorial > body text

Why is a MySQL \'IN\' query with a subquery significantly slower than with explicit values?

Barbara Streisand
Release: 2024-10-31 02:09:02
Original
627 people have browsed it

 Why is a MySQL

Performance Gap Between Subquery and Explicit Value "IN" Queries

Why is a MySQL "IN" query significantly slower when utilizing a subquery than when using explicit values?

Consider the following query:

<code class="sql">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')</code>
Copy after login

This query takes approximately 18 seconds to execute, despite the fact that the subquery alone completes in under 1ms.

However, when the subquery is replaced with explicit values:

<code class="sql">SELECT
COUNT(DISTINCT subscriberid)
FROM
em_link_data
WHERE
linkid IN (24899,24900,24901,24902);</code>
Copy after login

The query completes in less than 1 millisecond.

Explanation

The performance discrepancy arises from the way MySQL evaluates subqueries. Subqueries are executed every time they are encountered, which means that in the first query, MySQL is essentially executing seven million queries (subquery evaluation for each row in the "em_link_data" table). In contrast, when using explicit values, the subquery is evaluated only once.

Workaround

If rewriting the query using a JOIN is not an option, you can consider using a query cache to improve performance. The query cache stores the results of previously executed queries and reuses them if the same query is executed again. This can significantly reduce the execution time of subquery-heavy queries.

To enable the query cache, add the following line to your MySQL configuration file:

query_cache_type = 1
Copy after login

Restart MySQL for the changes to take effect.

The above is the detailed content of Why is a MySQL \'IN\' query with a subquery significantly slower than with explicit values?. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!