MySQL uses single-column indexes and multicolumn indexes

Discusses the MySQL select index when single-column and multicolumn indexes are used, as well as the leftmost prefix principle for multi-column indexes.

1. Single-Column indexing

In the performance optimization process, choosing which columns to create indexes on is one of the most important steps. There are two main types of columns that you can consider using indexes: columns that appear in the WHERE clause, columns that appear in the join clause. Consider the following query:

Select -- do not use   indexes  from Where FirstName='Mike'--  Consider using the   index and  LastName='Sullivan'--  Consider using an index

This query belongs to a simple query. Because age is referenced in the Select section, MySQL does not use it to restrict column selection operations. Therefore, it is not necessary to create an index of the age column for this query.

The following is a more complex example:

SelectPeople.age,--do not use indexesTown.name--do not use indexes fromPeople Left JOINTown onPeople.townid=Town.townid--consider using an indexWhereFirstName='Mike' --consider using an index andLastName='Sullivan' --consider using an index

As in the previous example, because FirstName and LastName appear in the WHERE clause, these two columns still have the necessary to create an index. In addition, because the Townid of the town table is listed in the JOIN clause now, we need to consider creating the index of the column.

So, can we simply assume that each column that appears in the WHERE clause and the JOIN clause should be indexed? Almost so, but not entirely. We also have to take into account the type of operator that compares the columns. MySQL uses the index only for the following operators: <,<=,=,>,>=,between,in, and sometimes like. The case in which you can use an index in a like operation is when another operand is not preceded by a wildcard character (% or _).

Select  from Where  like ' mich% '

This query will use the index, but the following query will not use the index.

Select  from Where  like ' %ike ';

2. Multi-column index

The index can be a single-column index or a multicolumn index. Let's take a concrete example to illustrate the differences between the two indexes. Suppose there is such a people table:

Create TABLEpeople (PeopleidSMALLINT  not NULLAuto_increment, FirstNameCHAR( -) not NULL, LastNameCHAR( -) not NULL, AgeSMALLINT  not NULL, TownidSMALLINT  not NULL,   PRIMARY KEY(Peopleid));

Here is the data we inserted into this people table:

The data fragment contains four people named "Mikes" (two of them Sullivans, two surname McConnells), two are 17 years of age, and a different name for Joe Smith. The primary purpose of this table is to return the corresponding Peopleid based on the specified user name, first name, and age. For example, we may need to find the Peopleid of a user named Mike Sullivan, who is 17 years of age:

Select Peopleid    from people    Where FirstName='Mike'and          lastname= ' Sullivan '  and age=n;

Since we don't want MySQL to scan the entire table every time it executes a query, we need to consider using an index.

First, we can consider creating an index on a single column, such as FirstName, LastName, or the Age column. If we create an index of the FirstName column (Alter TABLE people add index FirstName (firstname), MySQL will quickly limit the search to those firstname= ' Mike ' records through this index , and then search for other criteria on this "intermediate result set": It first excludes records whose lastname are not equal to "Sullivan", and then excludes records whose age is not equal to 17. When the record satisfies all search criteria, MySQL returns the final search results.

Because of the FirstName column index, MySQL is much more efficient than performing a full scan of the table, but we require that the number of logs scanned by MySQL still far exceeds what is actually needed. Although we can delete the index on the FirstName column, and then create an index of the LastName or age column, it seems that the efficiency of creating an index search is still similar, regardless of which column.

To improve search efficiency, we need to consider using Multi-column indexes . If you create a multi-column index for the three columns of FirstName, LastName, and age, MySQL can find the correct results with a single search! Here is the SQL command to create this multi-column index:

Alter TABLE people    ADD INDEX fname_lname_age (firstname,lastname,age);

Since the index file is saved in the B-tree format, MySQL can immediately go to the appropriate FirstName and then go to the appropriate LastName, and finally to the appropriate age. In the absence of any record of the scanned data file, MySQL correctly finds the target record of the search!

So, if you create a single-column index on the three columns of FirstName, LastName, and age, will the effect be the same as creating a multicolumn index of FirstName, LastName, and age?

The answer is no, the two are totally different. When we execute the query, MySQL can use only one index. If you have three single-column indexes, MySQL will try to select one of the most restrictive indexes. However, even the most restrictive single-column index is limited in its ability to be significantly less than a multicolumn index on the three columns of FirstName, LastName, and age.

3. Leftmost prefix in multi-column index (leftmost prefixing)

A multi-column index has another advantage, which is manifested by the concept of the leftmost prefix (leftmost prefixing). Continuing to consider the previous example, we now have a multi-column index on the FirstName, LastName, and age columns, which we call the index fname_lname_age. When the search condition is a combination of the following columns, MySQL uses the fname_lname_age index:

Firstname,lastname,age

Firstname,lastname

FirstName

On the other hand, it is equivalent to the index we created (Firstname,lastname,age), (Firstname,lastname), and (FirstName) on these column combinations. The following queries all have the ability to use this Fname_lname_age index:

 select  peopleid from   people  where  firstname=   '  ; 

Select  from people    Where FirstName='Mike' and LastName=' Sullivan ';

Select  from people    Where FirstName='Mike'

The following queries are not able to use this Fname_lname_age index:

Select  from people    Where LastName='Sullivan';

Select  from people    Where Age='n';

Select  from people    Where LastName='Sullivan' and age='  - ';

Reference:

http://greatwqs.iteye.com/blog/1897118

MySQL uses single-column indexes and multicolumn indexes