What should you pay attention to when using related queries in mysql?

1. Make sure there are indexes on the columns in the ON and USING clauses.

The order of associations must be considered when creating an index. When table A and table B are associated using column c, if the optimizer association order is A, B, then there is no need to create an index on the corresponding column of table A. Unused indexes will bring additional burden. Generally speaking, unless there are other reasons, you only need to create indexes on the corresponding columns of the second table in the association sequence.

2. Make sure that any expressions in GROUP BY and ORDER BY only involve columns in one table, so that MySQL can use indexes for optimization.

Example

Assuming that MySQL performs the association operation according to the association sequence A and B in the query, then the following pseudo code can be used to indicate how MySQL completes this Query:

outer_iterator = SELECT A.xx,A.c FROM A WHERE A.xx IN (5,6);
 
outer_row = outer_iterator.next;
 
while(outer_row) {
 
    inner_iterator = SELECT B.yy FROM B WHERE B.c = outer_row.c;
 
    inner_row = inner_iterator.next;
 
    while(inner_row) {
 
        output[inner_row.yy,outer_row.xx];
 
        inner_row = inner_iterator.next;
 
    }
 
    outer_row = outer_iterator.next;
 
}　　可以看到，最外层的查询是根据A.xx列来查询的，A.c上如果有索引的话，整个关联查询也不会使用。再看内层的查询，很明显B.c上如果有索引的话，能够加速查询，因此只需要在关联顺序中的第二张表的相应列上创建索引即可。

The above is the detailed content of What should you pay attention to when using related queries in mysql?. For more information, please follow other related articles on the PHP Chinese website!