Slow mysql subquery

When you use the explain tool to view the execution plan of the SQL statement, if the select_type field contains "DEPENDENTSUBQUERY", you should note that, you have fallen into the slow mysql subquery "pit... Here is a specific example of a query statement: SELECTgid, COUNT (id) ascountFROMshop_goodsg

When you use the explain tool to view the execution plan of the SQL statement, if the select_type field contains "DEPENDENT SUBQUERY", you should note that, you have fallen into the slow mysql subquery "pit... Here is a specific example of a query statement: SELECT gid, COUNT (id) as countFROM shop_goods g

When you use the explain tool to view the execution plan of the SQL statement, if the select_type field contains "DEPENDENT SUBQUERY", you should note that, you have fallen into the slow mysql subquery "pit "... Let's look at a specific example.

There is 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 WHERE sid IN (152.1666,1466114, 1466110,1466102, region) group by gid;

You can use explain to check that the keyword "dependent subquery" appears, which means that the first select statement of the SUBQUERY depends on the external query;

SUBQUERY: The first select in the subquery; dependent subquery: The first select in the subquery, depending on the external query.

In other words, the subquery Method for g2 depends on the outer G1. It means two steps:

Step 1: MySQL returns a large result set t1, whose data volume is rows = 850672 according to select gid, count (id) from shop_goods where status = 0 group by gid;

Step 2: each record in the above big result set t1 will form a new query statement with the subquery SQL: select gid from shop_goods where sid in (15... blabla .. 29) and gid = % t1.gid %. It means that the subquery needs to be executed for 0.85 million times ...... Even if indexes are used in the two queries, it is not surprising that the query is not slow;

As a result, the execution efficiency of the subquery is subject to the number of records in the outer query. It is better to split it into two separate query orders for execution.

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

If you do not want to split it into two independent queries, you can also join the temporary table for query, as shown in the following optimized SQL:

SELECT g1.gid, count (1) FROM shop_goods g1, (select gid from shop_goods WHERE sid in (latency, 1466110,1466102, latency) g2 where g1.status = 0 and g1.gid = g2.gid group by g1.gid;

I checked it with explain, and now I have a new keyword "DERIVED", which is used for subqueries IN THE from clause. MySQL recursively executes these subqueries, puts the results in the temporary table, and then performs the join operation;

The official meaning of DERIVED is that it is used for subqueries in the from clause. MySQL recursively executes these subqueries and places the results in the temporary table.

Section 4.4.1 of "Limitations of the MySQL Query Optimizer" in section 4.4 of High Performance MySQL "Correlated Subqueries) mysql will rewrite the subquery when processing subqueries. In general, we want to complete the subquery results from the inside out, and then use the subquery to drive the external query table to complete the query.

For example: select * from test where tid in (select fk_tid from sub_test where gid = 10); we generally think that the SQL Execution sequence is: in the sub_test table, obtain the fk_tid (, 6) Record Based on the gid, and then import it to test to obtain the query data.

However, the actual mysql processing method is as follows:

Select * from test where exists (select * from sub_test where gid = 10 and sub_test.fk_tid = test. tid );

Mysql will scan all the data in the test, and each piece of data will be uploaded to the subquery for association with sub_test. The subquery will not be executed first, so if the test table is large, performance issues will occur.