Slow mysql subquery and mysql subquery

Slow mysql subquery and mysql subquery
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.

The query speed in mysql is too slow. My query statement is a multi-table joint query statement, which is written as follows. Is it not very good to optimize ???

Let me look at it briefly. Your a1 here is actually a subquery.

I need to ask if your a2 to a6 are also subqueries.
If so, the efficiency is estimated to be a bit problematic.

Then let's make a further confirmation.
Whether all of your a1-a6 subqueries query an omdb. rt110celltrxrelatedoverviewcounters1 table.
That is, whether the same table is queried from a1 to a6.

For example, a1 and a2 query the same table. The same is true for a3 and a4. A5.
In this case, it is still possible to make optimization adjustments.

If the specific tables from a1 to a6 are completely different, it seems that there is no way to create some indexes on the SQL.

Slow like query speed in MySQL

This fuzzy search with % xx % is difficult to index...
So the statement itself cannot be optimized...