1, what is a subquery, table association query:
Subquery: Refers to using a SELECT query statement in the Select or WHERE clause in the main SQL statement; Select A.name, (select B.name from B where b.id=a.id) from a where a.name lik ' a %‘
Table Association query: refers to multiple tables union query; Select A.name,b.name from a A, where a.id=b.id and a.name like ' a% ';
Although Federated query performance is poor, there is a significant performance advantage over MySQL subqueries. MySQL's sub-query execution plan has been a big problem, although this problem has existed for many years, but has been released by all the stable version of the widespread, has not been much improved. While the authorities have recognized this issue early and pledged to resolve it as soon as possible, at least we have not yet seen which version of the issue has been better solved.
By default, a full table scan is used in a subquery, so the total I/o count = number of primary tables * sub-table. When the data in the main table, the child table is checked 1w, the overall performance of the subquery will be reduced immediately, and the drop will be very severe (the test found that it needs to be more than 10s)
For the above problem, the solution:
1) index or establish a foreign key relationship with the associated column in the child table:
There are two tables A, B, and the following are the execution plans using subqueries
A table structure:
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
MySQL Database SQL optimization--sub-query optimization
