MySQL Join (iii): Number of cycles within join optimization practice

This blog post describes how to optimize the number of internal loops . The number of times in the loop is affected by the number of records in the drive table, the more the number of drive table records, the more internal loops, the more inefficient the connection, so try to use a small table to drive large tables. Insert the test data first.

    CREATE TABLET1 (IDINT PRIMARY KEYauto_increment, typeINT    ); SELECT COUNT(*) fromT1; +----------+    | COUNT(*)|    +----------+    |    10000 |    +----------+    CREATE TABLET2 (IDINT PRIMARY KEYauto_increment, typeINT    ); SELECT COUNT(*) fromT2; +----------+    | COUNT(*)|    +----------+    |       - |    +----------+

Inside connect who when the driver table

In the actual business scenario, left join and right connections can be identified according to business requirements who are the driver table and who is the driver. But the inner connection is different, according to the idea of nested loop algorithm, the result set of connecting T2 and T2 within the T1 is the same. So who is connecting who? Remember one sentence, small table driver Large table can reduce the number of internal loops . The following forces the left table to join the right table with Straight_join. By the Way,stright_join is less popular, explained here, its role is equivalent to the internal connection, but forced to set the left table drive to the right. See this MySQL join for details (a): usage

EXPLAINSELECT *  fromT1 Straight_join T2 onT1.type=T2.type; +----+-------+------+------+-------+----------------------------------------------------+    |Id| Table |Type| Key  |Rows|Extra|    +----+-------+------+------+-------+----------------------------------------------------+    |  1 |T1|  All  | NULL | 10000 | NULL                                               |    |  1 |T2|  All  | NULL |    - |Usingwhere; UsingJoinBuffer (Block Nested Loop)|    +----+-------+------+------+-------+----------------------------------------------------+EXPLAINSELECT *  fromT2 Straight_join T1 onT2.type=T1.type; +----+-------+------+------+-------+----------------------------------------------------+    |Id| Table |Type| Key  |Rows|Extra|    +----+-------+------+------+-------+----------------------------------------------------+    |  1 |T2|  All  | NULL |    - | NULL                                               |    |  1 |T1|  All  | NULL | 10000 |Usingwhere; UsingJoinBuffer (Block Nested Loop)|    +----+-------+------+------+-------+----------------------------------------------------+

For the first query statement, T1 is the driver table, which has 10,000 records, the inner loop is 10,000 times, this is also?
For the second query statement, T2 is the driver table, which has 100 records, within the loop 100 times, feel good, I like!
The execution time of these SQL statements also illustrates the need to drive large tables with small tables when connecting inside.

Best practice: Get MySQL to judge directly

However, the number of records in the table will change, there is no once and for all the wording? Of course, MySQL's own optimizer will optimize the internal connection, the optimization strategy is the above-mentioned small table driver large table. So, in the future to write inside the connection do not tangle who connected who, directly let MySQL to judge it.

EXPLAINSELECT *  fromT1INNER JOINT2 onT1.type=T2.type; EXPLAINSELECT *  fromT2INNER JOINT1 onT1.type=T2.type; EXPLAINSELECT *  fromT1JOINT2 onT1.type=T2.type; EXPLAINSELECT *  fromT2JOINT1 onT1.type=T2.type; EXPLAINSELECT *  fromT1,t2WHERET1.type=T2.type; EXPLAINSELECT *  fromT2,t1WHERET1.type=T2.type; +----+-------+------+------+--------+----------------------------------------------------+    |Id| Table |Type| Key  |Rows|Extra|    +----+-------+------+------+--------+----------------------------------------------------+    |  1 |T2|  All  |  NULL|     - | NULL                                               |    |  1 |T1|  All  | NULL | 110428 |Usingwhere; UsingJoinBuffer (Block Nested Loop)|    +----+-------+------+------+--------+----------------------------------------------------+

The optimizer that connect sql,mysql in the above 6 strips are optimized.

MySQL Join (iii): Number of cycles within join optimization practice