MySQL index usage instructions (single-column index and multi-column index)

1. Single column index

Choosing which columns to create indexes on is one of the most important steps in the performance optimization process. There are two main types of columns that can be considered for indexing: columns that appear in the WHERE clause, and columns that appear in the JOIN clause. Consider the following query:

Select age ## Don't use index FROM people Where firstname='Mike' ## Consider using index AND lastname='Sullivan' ## Consider using index

This query is slightly different from the previous one, but still simple. Because age is referenced in the Select part, MySQL does not use it to restrict the column selection operation. Therefore, for this query, it is not necessary to create an index on the age column.

Here is a more complex example:

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

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

So, can we simply think that every column that appears in the Where clause and the Join clause should be indexed? Almost, but not quite. We must also consider the type of operator used to compare the columns. MySQL uses indexes only for the following operators: <, <=, =, >, >=, BETWEEN, IN, and sometimes LIKE.

An index can be used in a LIKE operation when the other operand does not begin with a wildcard character (% or _).

For example:

Select peopleid FROM people Where firstname LIKE 'Mich%'

This query will use the index; however, the following query will not.

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

2. Multi-column index

The index can be a single-column index or a multi-column index. Below we use specific examples to illustrate the difference between these two indexes. Suppose there is a people table like this:

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 the people table:

There are four people named "Mikes" in this data snippet (two with the last name Sullivans and two with the last name McConnells), two people aged 17, and one person with the unusual first name Joe Smith.

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

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 we execute 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 age. If we create an index on the firstname column (Alter TABLE people ADD INDEX firstname (firstname);), MySQL will quickly use this index to limit the search scope to those records where firstname='Mike', and then perform other search 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 records meet all the search conditions, MySQL returns the final search results.

Because the firstname column is indexed, MySQL is much more efficient than a full scan of the table, but we still require MySQL to scan far more records than it actually needs. Although we can delete the index on the firstname column and then create an index on the lastname or age column, overall, the search efficiency is similar regardless of which column the index is created on.

In order to improve search efficiency, we need to consider using multi-column indexes. If you create a multi-column index for firstname, lastname, and age, MySQL can find the correct result with 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 stored in 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 records in the data file, MySQL correctly found the target record!

So, if we create single-column indexes on the three columns firstname, lastname, and age respectively, will the effect be the same as creating a multi-column index on firstname, lastname, and age?

The answer is no, the two are completely different. When we execute a query, MySQL can only use one index. If you have three single-column indexes, MySQL will try to choose the most restrictive index. However, even the most restrictive single-column index is certainly much less restrictive than a multi-column index on the firstname, lastname, and age columns.

3. Leftmost Prefixing in Multi-column Indexes

There is another advantage of multi-column indexes, which is reflected in the concept called Leftmost Prefixing. Continuing to consider the previous example, now we have a multi-column index on the firstname, lastname, and age columns, and we call this index 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

From another perspective, it is equivalent to creating indexes on the combination of (firstname, lastname, age), (firstname, lastname), and (firstname). The following queries can all use the 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 the fname_lname_age index:

SELECT peopleid FROM people  
Where lastname='Sullivan';

SELECT peopleid FROM people  
Where age='17';

SELECT peopleid FROM people  
Where lastname='Sullivan' AND age='17';

This article ends here. Later, the editor of 123WORDPRESS.COM will introduce more articles about MySQL index to you.