A study of federated index based on MySQL query optimizer

Source: Internet
Author: User
Tags mysql query

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.