Differences between single-column and combined indexes in MySQL _ 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: CREAT MySQL single column index is often seen in MySQL databases, the differences between single-column index and composite index in MySQL may not be well understood by many people. 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"