Mysql-optimized BTree index and mysqlbtree Index

Mysql-optimized BTree index and mysqlbtree Index

Start with a question:

Suppose a table has a joint index (c1, c2, c3, c4). Only c1, c2, and c3 parts of the joint index can be used.
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

Who knows the following A-E can use the index !! Why?

OK; Start

Create a table:

Insert into t values ('a1 ', 'a2', 'a3 ', 'a4', 'a5 '), ('b1', 'b2', 'b3 ', 'b4 ', 'b5 ');

Insert data:

Insert into t values ('a1 ', 'a2', 'a3 ', 'a4', 'a5 '), ('b1', 'b2', 'b3 ', 'b4 ', 'b5 ');

Add an index:

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

The first case indicates that c1, c2, c3, and c4 are used.

Slightly changed:

Use group by to generate temporary files and sort them.

Order? Similar to above

A little change, analysis: It's easy to know how it works!

What is the answer to the above question? I don't know!

Summary Rules are available: