A study of federated index based on MySQL query optimizer

Without a statement, speak directly:

The federated index in the Babysitter_account table is as follows (developing a federated index that the small partners have built themselves, did you find it inappropriate?) ）：

KEY ' flag ' (' flag ', ' user_id ', ' account_id ')

Used to think:

1.SELECT Account_id,weibo_id,weibo_type from Babysitter_account WHERE user_id between and 10000 and flag=0;
2.SELECT Account_id,weibo_id,weibo_type from Babysitter_account WHERE flag=0 and user_id between and 10000;

The first SQL fails to hit the index, the second one hits,

But the reality is that two SQL hits are indexed exactly the same way.

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/59/83/wKioL1TV81TwtnIMAADE6Pd6IIY023.jpg "title=" Qq20150207190757.png "alt=" Wkiol1tv81twtnimaade6pd6iiy023.jpg "/>

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/59/87/wKiom1TV8mXiRd-7AADjD-7ogy0933.jpg "title=" Qq20150207190808.png "alt=" Wkiom1tv8mxird-7aadjd-7ogy0933.jpg "/>

Because the MySQL query optimizer optimizes SQL, the optimizer chooses the appropriate driver expression based on the access type, which is the same for the two SQL driver expressions.

The query optimizer's query optimization selection rules for where with and are as follows:

The format of the query is:<condition> and <condition>

Steps to optimize:

1) If none of the two columns are indexed, use a full table scan.

2) Otherwise, if one of the columns has a better access type (for example, one has an index and the other has no index; or, one is a unique index and the other is a non-unique index), the column is used as the driver expression.

3) Otherwise, if two columns each have an index, and the access type of the two criteria corresponds to the same, then select the first defined index when the index is defined.

Both conditions are indexed and identical, so the access types are the same, and there are no sequential definitions, so the execution is exactly the same.

PS:ACCOUNT_ID Plus in the Federated index is completely meaningless, itself is the primary key, priority hit, only slow down the storage speed.

This article is from the "I Believe" blog, please be sure to keep this source http://mrcelite.blog.51cto.com/2977858/1612695

A study of federated index based on MySQL query optimizer