in the slow query optimization, the probability of the optimization of the In keyword is quite high. In fact, the In keyword appears in the SQL optimization is not very difficult, it is important to be familiar with the SQL application scenario can also be said to be business logic.
First, give a recent example (business: Query the number of articles under this tab):
Select COUNT (*) from cms_article CA where a tagid in (select cat.tag_id from Cms_article_tag cat where ca.id=cat.article_i D
look at the EXPLAIN results:
quote MySQL Official document in a sentence: A typical case for poor subquery performance was when the
IN subquery returns A small number of rows But the outer query returns a large number of rows to being compared to the subquery result.
it can be seen from the EXPLAIN results that the in subquery sweeps 2 rows, while the other query scans a large number of rows, in full compliance with the official document slow query situation.
second, how to optimize it? at the beginning of this article, I said, familiar with business logic, and then very good change. solution for referencing the official MySQL document: The optimizer is more mature for joins than for subqueries, so in many cases a statement that uses a Subquery can executed more efficiently if you rewrite it as a join.[is familiar with the business, then most of the situation can be changed to join the way to optimize]
optimized post-sql:
Select COUNT (*) from cms_article CA right JOIN cms_article_tag cat on cna.id = cnat.article_id WHERE cnat.tag_id= a TagID
Explain results:
The number of query rows dropped sharply, as long as the complexity of the 25*1 to complete the query.
iii. References
MySQL official website
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL tuning----in
