MySQL sub-query slow problem

  When you are using the Explain tool to view the run schedule of the SQL statement. If the Select_type field appears in theDEPENDENT subquery"When you have to pay attention." You have fallen into the MySQL sub-query slow "pit" ...

Let's look at a detailed example below

There is such a query statement:

select Gid,count (ID) as COUNT from shop_goods G1 WHERE status =0 and GID in (SELECT gid from Shop_goods G2 WH ERE SID in (1519066,1466114,1466110,1466102,1466071,1453929))GROUP by GID;

I looked at it with explain. Keyword "DEPENDENT subquery" appears. means that the first select of a subquery relies on an external query;

subquery: The first select in a subquery. DEPENDENT subquery: The first select in a subquery depends on the outside query .

Other words. Is that the subquery's query to G2 depends on the outer G1 query .

It means two steps:

The first step. MySQL is based on the select Gid,count (ID) from shop_goods where the status=0 GROUP by GID gets a large result set T1. Its data volume is rows=850672;

In the second step, each record in the above large result set T1 will consist of a new query statement with the subquery SQL: Select GID from Shop_goods where Sid in (15...blabla. ) and gid=%t1.gid%. is equal to saying that the subquery will run 850,000 times ... Even though these two-step queries are indexed, they are not slow;

As a result, the efficiency of a subquery is constrained by the number of records in the outer query, which is not as good as splitting into two separate query sequences .

The general optimization strategy for such a statement is to split into two query statements. If you do not want to split into two independent queries, you can also query with a temporary table join:

You don't want to split into two independent queries, you can also query with a temporary table , such as the following optimized sql:

select G1.gid,count (1)   (select Gid from Shop_goods, WHERE SID in (1519066,1466114,1466110,1466102,1466071,1453929)) g2  Span style= "font-family: ' Courier New '; font-size:14px; " >where g1.status=0 and  g1.gid=g2.gid 

With explain look, this time there is a new keyword "DERIVED", meaning is used in the FROM clause in the case of subqueries. MySQL will run these subqueries recursively, put the results in the temporary table, and then do the join operation;

the official meaning of DERIVED is: used in cases where there is a subquery in the FROM clause. MySQL will run these subqueries recursively, putting the results in a temporary table.

Section 4.4 of the high-performance MySQL "MySQL query optimizer limitations (limitations of the MySQL Optimizer)", section 4.4.1, "Associating subqueries (correlated subqueries)" There is a similar discussion: When MySQL is processing a subquery. Will overwrite the subquery. In general, we want the results of the subquery to be completed first, from the inside out. Then use the subquery to drive the outer query of the table, complete the query.

For example: the SELECT * from Test where the TID in (select Fk_tid from Sub_test where gid=10); Usually we feel that the SQL is run in the order of: Sub_test table in accordance with the GID get f K_tid (2,3,4,5,6) record. and then to test. Bring in tid=2,3,4,5,6, get query data.

However, the actual MySQL processing method is:

SELECT * FROM test where exists (select * from Sub_test where gid=10 and Sub_test.fk_tid=test.tid); /c5>

MySQL will scan all the data in test, each data will be sent to the subquery associated with the sub_test, the subquery will not be run first, so if the test table is very large, then performance will be faulted.

Copyright notice: This article Bo Master original articles, blogs, without consent may not be reproduced.

MySQL sub-query slow problem