Single and multi-column indexes in MySQL database Indexes

The following articles mainly introduce MySQL database indexes, that is, single-column indexes and multi-column indexes, and examples of SQL commands for multi-column indexes, I hope you will have a better understanding of the MySQL database index content after browsing.

To improve search efficiency, we need to consider using multiple MySQL database indexes. If you create a multi-column index for the columns firstname, lastname, and age, MySQL only needs to retrieve the correct results once! The following is an 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 B-tree format, MySQL can immediately convert it to the appropriate firstname, then to the appropriate lastname, and finally to the appropriate age. Without scanning any record of the data file, MySQL finds the target record correctly!

So, if you create a single column index on the columns firstname, lastname, and age respectively, will the effect be the same as creating a MySQL database index on multiple columns of firstname, lastname, and age? The answer is no. The two are completely different. When we perform a query, MySQL can only use one index. If you have three single-column MySQL database indexes, MySQL will try to select the most restrictive index.

However, even if it is the most restrictive single-column index, its capacity is certainly far lower than the multiple-column index of the three columns firstname, lastname, and age.

The above content is an introduction to the MySQL database index. I hope you will get something better.