How MySQL works from the left join to the join to the query plan to the Federated Index

Left Join

Left join in no brain use, will be large table drive small table, trigger Cartesian set, slow efficiency

Join will auto small table drive Large table

Reference:

Deepen your understanding of query plans from an example of MySQL left join optimization
http://luxuryzh.iteye.com/blog/1976004

Related explain query plan explanation

Type is divided into several categories, such as system/const/eq_ref/ref/range/index/all, the efficiency of the connection
Good.. "Poor

All: wood useful to index

Index, use the

Range, index between in etc Range class

Ref non-primary key index

Const PRIMARY KEY index

Reference:

MySQL Execution plan interpretation-Ggjucheng-Blog Park
Http://www.cnblogs.com/ggjucheng/archive/2012/11/11/2765237.html

The principle of federated indexing

Suppose A is 1,2,3,b and also a

Set A/b as a federated index

is sorted in the tree (A, B)

1 1, 1 2, 1 3,

2 1, 2 2, 2 3,

3 1, 3 2, 3 3

Just look at a, a is ordered, and B is not.

So in the query, if both are used in the where, then in no particular order, MySQL will automatically use the index,

If only a is used in the where, it is also possible because a has a sequential

If only B is used, the query plan has a type of all, that is, no index is used.

Reference:

MySQL InnoDB Federated Index Learning-ops that's something
http://www.ywnds.com/?p=11204

How MySQL works from the left join to the join to the query plan to the Federated Index