Rules for using mysql joint indexes

A joint index is also called a composite index. For composite indexes: MySQL uses the fields in the index from left to right. A query can use only part of the index, but only the leftmost part. For example, the index is key index (a,b,c). It can support the search of three combinations of a | a,b | a,b,c, but does not support the search of b,c. When the leftmost field is a constant reference, the index is very effective.

Let’s start with an interesting question:

Assume that a table has a joint index (c1, c2, c3, c4). Which fields use the index?
A where c1=x and c2=x and c4>x and c3=x
B where c1=x and c2=x and c4=x order by c3
C where c1=x and c4= x group by c3,c2
D where c1=? and c5=? order by c2,c3
E where c1=? and c2=? and c5=? order by c2,c3

Let’s get started:

First create the table:

CREATE TABLE t(
c1 CHAR(1) not null,
c2 CHAR(1) not null,
c3 CHAR(1) not null,
c4 CHAR(1) not null,
c5 CHAR(1) not null
)ENGINE myisam CHARSET UTF8;

There are 5 fields from c1 to c5. Special note that the field types are all fixed-length char(1) types, and are not empty. The character set is utf8 (related to the number of bytes used to calculate the index)

Create an index:

alter table t add index c1234(c1,c2,c3,c4);

Insert 2 records: insert into t VALUES('1','1','1','1','1'),('2','2','2','2','2')

Use MySql Explain to start analyzing the results of the question:

Option A:

As can be seen from the results, c1, c2, c3, and c4 all use the index, and we slightly change the result of A:

After removing the c2 condition:

According to the leftmost index principle, the c2 field does not use an index, and the fields after c2 cannot use an index. In the following two figures, we compare the leftmost index principle:

The results in the figure above show that directly using c3 is a full table query and the index cannot be used. Therefore, the premise for using the index for the c3 field is that both the c1 and c2 fields use indexes.

That is the leftmost principle of the index (left prefix principle).

Option B:

The key_len length indicates that the index is used in the c1 and c2 fields. Extra shows that no temporary table is used for sorting, which means that the index is used for sorting, but it is not calculated in the key_len value, and it does not serve the purpose of connecting to c4, which means that the index is broken at c3.

In fact, the sorting is done directly by using the joint index, that is, by using the c1234 joint index, c2 under c1, c3 under c2, and c4 under c3 are already in order. So the sorting actually uses the index, but the c3 field does not use the index. (I always felt a little awkward when writing this paragraph. I don’t know if I understand it correctly. It still needs further research)

Option C:

When using group by, a temporary file is usually generated first and then sorted. However, when the field order is c2, c3, the temporary table is not used for sorting, but the index is used for sorting. When the group by fields are c3, c2, the index is not used for grouping and sorting because the order is inconsistent with the index field order.

Determined by the key_len length, only the c1 field uses the index.

Option D:

order by is similar to group by. When the field order is consistent with the index, the index will be used for sorting; when the field order is inconsistent with the index, the index will not be used.

Determined by the key_len length, only the c1 field uses the index.

Option E:

In fact, the result analysis of option E has been analyzed in the results of ABCD above. Here, only the c1 and c2 fields use this index.

To sum up the answers to the questions above:

A: All four fields use this index.

B: c1, c2 fields use this index

C:c1 field uses this index

D:c1 field uses this index

E: The c1 and c2 fields use this index.

Summarize:

The leftmost principle (left prefix principle) of the index, such as the joint index of (c1, c2, c3, c4....cN), the where condition is used in the order of the fields in which the index is established (it does not mean that the and condition must be written in order). If there is no condition for a column in the middle, or if like is used, the subsequent columns cannot use the index.

Indexes can also be used for grouping and sorting. Grouping requires sorting first, then calculating the average, etc. Therefore, in grouping and sorting, if the order of the fields can follow the order of the indexed fields, the ordered nature of the index can be utilized.

This is the end of this article about the usage rules of MySQL joint indexes. For more relevant MySQL joint index content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!