Shulou(Shulou.com)05/31 Report--

This article mainly explains the difference between MySQL single-column index and combined index. The content of the explanation in this article is simple and clear, and it is easy to learn and understand. Please follow the train of thought of Xiaobian to study and learn the difference between MySQL single-column index and combined index.

What are the differences between MySQL single-column indexes and combined indexes

To visualize the two, create another table:

CREATETABLEmyIndex (NOTNULL,i_AgeINTNOTNULL,i_SchoolIDINTNOTNULL,PRIMARYKEY NameVARCHAR (50) NameVARCHAR (50) i_testID)

Among the 10000 records, there are 5 vc_Name= "erquan" records distributed in 7, 8 and 8, but the combination of city,age,school is different.

Take a look at this T-SQL:

SELECTi_testIDFROMmyIndexWHEREvc_Name='erquan'ANDvc_City=' Zhengzhou 'ANDi_Age=25

First consider building an MySQL single-column index:

An index is established on the vc_Name column. When executing T-SQL, MYSQL quickly targets five records in vc_Name=erquan and takes them out and puts them in an intermediate result set. In this result set, we first exclude the records whose vc_City is not equal to "Zhengzhou", then exclude the records whose i_Age is not equal to 25, and finally screen out the only records that meet the criteria.

Although the index has been established on vc_Name, and MYSQL does not have to scan the whole table when querying, the efficiency has been improved, but it is still a long way from our requirements. Similarly, the efficiency of MySQL single-column indexes established in vc_City and i_Age is similar.

In order to further extract the efficiency of MySQL, it is necessary to consider the establishment of a composite index. Is to build the vc_Name,vc_City,i_Age into an index:

ALTERTABLEmyIndexADDINDEXname_city_age (vc_Name (10), vc_City,i_Age)

When building a table, the length of vc_Name is 50. Why use 10 here? Because in general, the length of the name will not exceed 10, which will speed up the index query, reduce the size of the index file, and improve the update speed of INSERT.

What are the differences between MySQL single-column indexes and combined indexes

When T-SQL is executed, MySQL does not need to scan any records to find a unique record.

Someone must ask, if you set up a single-column index on vc_Name,vc_City,i_Age and let the table have three single-column indexes, will the query be as efficient as the combined index mentioned above? It's very different, much lower than our combined index. Although there are three indexes at this time, MySQL can only use one of the single-column indexes that it seems to be the most efficient.

The establishment of such a combined index is actually equivalent to the establishment of separate indexes

Vc_Name,vc_City,i_Agevc_Name,vc_Cityvc_Name

Such three combined indexes! Why is there no composite index like vc_City,i_Age? This is due to the result of the leftmost prefix of the mysql composite index. The simple understanding is to start with the leftmost combination. Not all queries that contain these three columns will use this composite index, but the following T-SQL will:

SELECT*FROMmyIndexWHREEvc_Name= "erquan" ANDvc_City= "Zhengzhou SELECT*FROMmyIndexWHREEvc_Name=" erquan "

The following will not be used:

SELECT*FROMmyIndexWHREEi_Age=20ANDvc_City= "Zhengzhou" SELECT*FROMmyIndexWHREEvc_City= "Zhengzhou"

Thank you for your reading, the above is the content of "the difference between MySQL single-column index and combined index". After the study of this article, I believe you have a deeper understanding of the difference between MySQL single-column index and combined index. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.