Differences between MySQL single-column indexes and composite indexes

MySQL single-column index is what we often see in MySQL database, the difference between MySQL single-column index and composite index may be a lot of people do not know very well, the following for you to analyze the main differences, for your reference to learn.

To visually compare the two, build a table:

CREATE TABLE Myindex (I_testid INT NOT NULL auto_increment, Vc_name varchar (.) NOT NULL, vc_city varchar (50) Not NULL, I_age int isn't null, i_schoolid int NOT null, PRIMARY KEY (I_testid));

In these 10,000 records, 7 of the 8 are distributed in 5 vc_name= "Erquan" records, but City,age,school combinations are different.
Look at this T-sql:

SELECT I_testid from Myindex WHERE vc_name= ' Erquan ' and vc_city= ' Zhengzhou ' and i_age=25;

First consider building a MySQL single-column index:

An index was established on the Vc_name column. When executing T-SQL, MYSQL quickly locks the target on the 5 records of Vc_name=erquan and takes it out to a middle result set. In this result set, the first rule out vc_city not equal to "Zhengzhou" record, and then exclude i_age not equal to 25 of the record, and finally filtered out the only qualified records.

Although the index is built on the vc_name, MySQL does not have to scan the whole table when querying, but the efficiency is improved, but there is a certain distance from our request. Similarly, the MySQL single-column indexes established separately in vc_city and i_age are similar in efficiency.

To further extract the efficiency of MySQL, it is necessary to consider building a composite index. is to build the Vc_name,vc_city,i_age into an index:

ALTER TABLE myindex ADD INDEX name_city_age (Vc_name (Ten), vc_city,i_age);

When building a table, the length of the vc_name is 50, why use 10 here? Because the length of the name does not typically exceed 10, this speeds up the index query, reduces the size of the index file, and increases the update speed of the INSERT.
When executing T-SQL, MySQL does not need to scan any records to find a unique record.

There must be someone to ask, if you set up a single-column index on the vc_name,vc_city,i_age, so that the table has 3 single-column index, query and the above combination index efficiency? Very different, far below our combined index. Although there are three indexes at this point, MySQL can only use one of the single-column indexes that it considers to be the most efficient.

The establishment of such a composite index is actually equivalent to establishing a separate

Vc_name,vc_city,i_age vc_name,vc_city Vc_name

Such a combination of three indexes! Why is there no such combination index as vc_city,i_age? This is because the MySQL composite index is the result of the "leftmost prefix". The simple understanding is only from the left to the beginning of the combination. It is not just that the combined index is used for queries that contain these three columns, and several of the following T-SQL is used:

SELECT * from Myindex whree vc_name= "Erquan" and vc_city= "Zhengzhou" select * from Myindex whree vc_name= "Erquan"

And the next few are not used:

SELECT * from Myindex whree i_age=20 and vc_city= "Zhengzhou" select * from Myindex whree vc_city= "Zhengzhou"

Differences between MySQL single-column indexes and composite indexes