MySQL multi-column index classic case

A classic multi-column index case, such as the title:

假设某个表有一个联合索引（c1,c2,c3,c4）一下——只能使用该联合索引的 c1,c2,c3 部分Awhere c1=x and c2=x and c4>x and c3=xB where c1=x and c2=x and c4=x order by c3C where c1=x and c4= x group by c3,c2D where c1=x and c5=x order by c2,c3E where c1=x and c2=x and c5=? order by c2,c3

Create a test table

create table test (    c1 tinyint(1) not null default 0,    c2 tinyint(1) not null default 0,    c3 tinyint(1) not null default 0,    c4 tinyint(1) not null default 0,    c5 tinyint(1) not null default 0,    index c1234(c1,c2,c3,c4));//插入两条数据insert into test values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);insert into test values (2,4,5,8,7),(1,3,5,8,4),(3,4,2,9,6);

Analysis A = C1=x and C2=x and c4>x and c3=x <== equivalent ==> c1=x and C2=x and C3=x and c4>x
C1,C2,C3,C4 can use it all.

Analysis b = = select * FROM test where c1=1 and c2=2 and c4=3 order by C3
C1, c2 index is used, on the basis of C2 used to the index, C3 is ordered, so no additional sorting, and C4 does not play a role

Analysis c = select * from test where c1=1 and c4=2 GROUP by C3,C2
The C1 index is used only because the order of group by C3,C2 cannot take advantage of the C2,C3 index

Analysis D = select * FROM test where c1=1 and c5=2 order by C2,C3
C1 is determined on the basis that C2 is orderly, C2 under C3 is orderly, so c2,c3 plays a role in sequencing. Therefore, it is useless to filesort

Analysis E = select * from test where c1=1 and c2=3 and c5=2 order by C3;
Because the value of C2 is both fixed and not considered when participating in a sort

MySQL multi-column index classic case