Differences between single-column and combined indexes in MySQL

MySQL Single-Column index is often seen in MySQL databases. Many people may not fully understand the differences between MySQL Single-Column index and composite index. The following describes the main differences between the two, for your reference.

To visually compare the two, create another table:

 
 

  
  
CREATE TABLE myIndex ( i_testID INT NOT NULL AUTO_INCREMENT, vc_Name VARCHAR(50) NOT NULL, vc_City VARCHAR(50) NOT NULL, i_Age INT NOT NULL, i_SchoolID INT NOT NULL, PRIMARY KEY (i_testID) ); 
 
 

Five vc_Name = "erquan" records are distributed in the Top 7 and bottom 8 of the 10000 records, except that the combinations of city, age, and school are different.
Look at this T-SQL:

 
 

  
  
SELECT I _testID FROM myIndex WHERE vc_Name = 'erquanc' AND vc_City = 'zhengzhou 'AND I _Age = 25;
 
 

First, create a single MySQL index:

An index is created on the vc_Name column. When the T-SQL was executed, MYSQL quickly locked the target to five records in vc_Name = erquan and pulled them out to the intermediate result set. In this result set, records with vc_City not equal to "Zhengzhou" are excluded, records with I _Age not equal to 25 are excluded, and a unique matching record is filtered out.

Although an index is created on vc_Name, MYSQL does not need to scan the entire table during query, which improves the efficiency, but there is still some distance from our requirements. Similarly, the efficiency of single-column MySQL indexes created in vc_City and I _Age is similar.

To further extract MySQL efficiency, you must consider establishing a composite index. Vc_Name, vc_City, and I _Age are built into an index:

 
 

  
  
ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10),vc_City,i_Age); 
 
 

When creating a table, the length of vc_Name is 50. Why is 10 used here? In general, the length of the name will not exceed 10, which will accelerate the index query speed, reduce the size of the index file, and increase the INSERT update speed.
When you run the T-SQL, MySQL does not need to scan any records to find a unique record.

Someone must ask, if you create a single column index on vc_Name, vc_City, and I _Age respectively, so that the table has three single column indexes, will the query efficiency be the same as that of the preceding composite indexes? The difference is far lower than our combined index. Although there are three indexes at this time, MySQL can only use one of them, which seems to be the most efficient single-column index.

The establishment of such a composite index is actually equivalent to the establishment

 
 

  
  
vc_Name,vc_City,i_Age  
  
  
vc_Name,vc_City  
  
  
vc_Name 
 
 

These three composite indexes! Why are there no composite indexes such as vc_City and I _Age? This is because mysql Composite Index "leftmost prefix" results. A simple understanding is to combine only from the leftmost. Not as long as the query contains these three columns will use the composite index, the following T-SQL will use:

 
 

  
  
SELECT * FROM myIndex WHREE vc_Name = "erquan" AND vc_City = "Zhengzhou"
  
  
SELECT * FROM myIndex WHREE vc_Name = "erquan"
 
 

The following are not used:

 
 

  
  
SELECT * FROM myIndex WHREE I _Age = 20 AND vc_City = "Zhengzhou"
  
  
SELECT * FROM myIndex WHREE vc_City = "Zhengzhou"
 
 

Take you to the DB2 Character Set and MySql Character Set

Using SSIS to build MySQL monitoring tools

View MySQL database table commands

What is Mysql table sharding?

Provides you with an in-depth understanding of Mysql external locks