MySQL index and leftmost prefix principle

Source: Internet
Author: User
Tags mysql index

These two days to see the "Build high-performance Web site" This book, feel write is really good, a lot of actual projects will encounter problems are mentioned, today see a most left prefix principle, have heard before, but have not understood, today checked the next.

Understanding single-column indexes, multi-column indexes, and leftmost prefix principles with an instance

Example: Now we want to find out the user ID that meets the following criteria:
Mysql>select ' uid ' from people WHERELName ' = ' Liu ' and ' fname ' = ' zhiqun ' and ' age ' =26
because we do not want to scan the entire table, we consider using an index.

Single-column index:
alter TABLE people ADD INDEX lname (lname); indexes the lname column so that the scope is limited to the result set of Lname= ' Liu ' 1, then scans the result set 1, produces a result set that satisfies fname= ' Zhiqun ' 2, then scans the result set 2, finds the age=26 result set 3, which is the final result.

because of the index of the lname column, it is much more efficient than performing a full scan of the table, but the number of records we require to scan still goes far beyond what is actually needed. Although we can delete the index on the lname column, and then create an index of the fname or age column, the efficiency of the index search is still similar regardless of which column is created.

2. Multi-column index:
ALTER TABLE People ADD index lname_fname_age (lame,fname,age);

Note: When executing a query in MySQL, only one index can be used, and if we are building an index on lname,fname,age, we can only use one index when executing the query, and MySQL chooses the most rigorous index to get the lowest number of result set records.

3. Leftmost prefix: As the name implies, is the leftmost priority, the above example we created Lname_fname_age Multi-column index, equivalent to create a (lname) single-row index, (lname,fname) composite Index and (lname,fname,age) Combined index.

Note: When creating a multicolumn index, the most frequently used column in the WHERE clause is placed on the leftmost line, depending on the business requirements.

MySQL index and leftmost prefix principle

Related Article

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.