MySQL Indexing Instructions of Single Column Indexing and Multi Column Indexing

1. Single-column index

Choosing which columns to create indexes on is one of the most important steps in the performance tuning process. There are two main types of columns that can be considered for using indexes: columns that appear in the Where clause and columns that appear in the join clause. Please look at the following query:

Select age ##  Do not use indexes   
FROM people Where firstname='Mike' ##  Consider using indexes   
AND lastname='Sullivan' ##  Consider using indexes  

This query is slightly different from the previous query, but it is still a simple query. Since age is referenced in the Select section, MySQL does not use it to restrict column selection operations. Therefore, for this query, it is unnecessary to create an index of age column.

Here's a more complex example:

Select people.age, ## Do not use indexes   
town.name ## Do not use indexes   
FROM people LEFT JOIN town ON people.townid=town.townid ## Consider using indexes   
Where firstname='Mike' ## Consider using indexes   
AND lastname='Sullivan' ## Consider using indexes  

As in the previous example 1, since firstname and lastname appear in the Where clause, it is still necessary to create an index for these two columns. In addition, since the townid listing of the town table is now in the join clause, we need to consider creating an index for that column.

So, can we simply think that we should index every column that appears in the Where clause and the join clause? Almost so, but not completely. We must also consider the operator types that compare columns. MySQL Indexes are used only for the following operators: < , < =, =, > , > =, BETWEEN, IN, and in some cases LIKE.

The case where an index can be used in an LIKE operation is the case where another operand does not begin with a wildcard character (% or _).

For example:

Select peopleid FROM people Where firstname LIKE 'Mich%' 

This query will use indexes; However, the following query does not use indexes.

Select peopleid FROM people Where firstname LIKE '%ike'; 

2. Multi-column index

An index can be a single-column index or a multi-column index. Let's illustrate the difference between these two indexes through concrete examples. Suppose you have an people table:

Create TABLE people (  
peopleid SMALLINT NOT NULL AUTO_INCREMENT,  
firstname CHAR(50) NOT NULL,  
lastname CHAR(50) NOT NULL,  
age SMALLINT NOT NULL,  
townid SMALLINT NOT NULL,  
PRIMARY KEY (peopleid) ); 

Here is the data we inserted into this people table:

In this data fragment, there are four people with the name "Mikes" (including two last names Sullivans and two last names McConnells), two people aged 17, and one Joe Smith with a different name.

The main purpose of this table is to return the corresponding peopleid based on the specified user's last name, first name, and age. For example, we might need to find peopleid for a 17-year-old user named Mike Sullivan:

Select peopleid 
FROM people  
Where firstname='Mike'  
   AND lastname='Sullivan' AND age=17; 

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

First, we can consider creating an index on a single column, such as an firstname, lastname, or age column. If we create an index of the firstname column (Alter TABLE people ADD INDEX firstname (firstname);) MySQL will use this index to quickly limit the search to those records where firstname = 'Mike', and then search for other conditions on this "intermediate result set": it first excludes those records where lastname is not equal to "Sullivan", and then excludes those records where age is not equal to 17. When the record meets all the search criteria, MySQL returns the final search results.

Because of the index of the firstname column, the efficiency of MySQL is much higher than that of performing a full scan of the table, but the number of records we require to scan MySQL still far exceeds the actual need. Although we can delete the index on the firstname column and create the index on the lastname or age column, overall, the search efficiency is similar regardless of which column is created.

In order to improve the search efficiency, we need to consider using multi-column indexes. If you create a multi-column index for the three columns firstname, lastname, and age, MySQL can find the correct results in just one search! Here is the SQL command to create this multi-column index:

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

Because the index file is saved in the B-tree format, MySQL can immediately go to the appropriate firstname, then to the appropriate lastname, and finally to the appropriate age. Without scanning any 1 record in the data file, MySQL correctly finds the target record of the search!

Then, if a single-column index is created on the three columns of firstname, lastname and age, is the effect the same as creating a multi-column index of firstname, lastname and age?

The answer is no, they are completely different. When we execute the query, MySQL can only use 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 certainly much less restrictive than the multi-column index on the three columns of firstname, lastname and age.

3. Leftmost prefix in multi-column index (Leftmost Prefixing)

Multi-column indexes have another advantage, which is manifested through a concept called the leftmost prefix (Leftmost Prefixing). Continuing with the previous example, we now have a multi-column index on firstname, lastname, age columns, which we call fname_lname_age. MySQL uses the fname_lname_age index when the search condition is a combination of the following columns:

firstname, lastname, age
firstname, lastname
firstname

On the other hand, it is equivalent to creating indexes on combinations of columns (firstname, lastname, age), (firstname, lastname), and (firstname). The following queries can all use this fname_lname_age index:

Select peopleid FROM people  
Where firstname='Mike' AND lastname='Sullivan' AND age='17';  

Select peopleid FROM people  
Where firstname='Mike' AND lastname='Sullivan';  

Select peopleid FROM people  
Where firstname='Mike';  

The following queries cannot use this fname_lname_age Index:

Select people.age, ## Do not use indexes   
town.name ## Do not use indexes   
FROM people LEFT JOIN town ON people.townid=town.townid ## Consider using indexes   
Where firstname='Mike' ## Consider using indexes   
AND lastname='Sullivan' ## Consider using indexes  

0

Select people.age, ## Do not use indexes   
town.name ## Do not use indexes   
FROM people LEFT JOIN town ON people.townid=town.townid ## Consider using indexes   
Where firstname='Mike' ## Consider using indexes   
AND lastname='Sullivan' ## Consider using indexes  

1

Select people.age, ## Do not use indexes   
town.name ## Do not use indexes   
FROM people LEFT JOIN town ON people.townid=town.townid ## Consider using indexes   
Where firstname='Mike' ## Consider using indexes   
AND lastname='Sullivan' ## Consider using indexes  

2

That's the end of this article, and later this site will introduce you to more related articles about mysql index.