MySQL index and leftmost prefix principle

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