MySQL Multi-column index

MySQL multi-column index is a frequently encountered problem, how to effectively hit the index, is the focus of this article to explore.

A multi-column index uses the Btree, which is the balanced binary tree. In simple terms, it is a quick way to index a good order. Its principle is to follow the index of the left prefix.

Multiple indexes are used from the left to the right to use the entire multicolumn index.

Let me start by creating a simple table to do the experiment:

CREATE TABLE T6 (

C1 char (1) NOT null default ' ',

C2 char (1) NOT null default ' ',

C3 char (1) NOT null default ' ',

C4 char (1) NOT null default ' ',

C5 char (1) NOT null default ' ',

Key (C1,C2,C3,C4,C5)

) engine MyISAM charset UTF8;

INSERT into some data separately.

Make the following queries, respectively:

1.

Explain select * from T6 where c1= ' a ' and c2= ' B ' and c4> ' a ' and c3= ' C ' \g;
1. Row ***************************
Id:1
Select_type:simple
Table:t6
Type:range
Possible_keys:c1
Key:c1
Key_len:12
Ref:null
Rows:2
Extra:using where
1 row in Set (0.00 sec)

The C1,C2,C3,C4 index is used here, and the special C4 is a range query, so the type is range, and C1,C2,C3 is hit index in turn.

2.

Explain select * from T6 where c1= ' a ' and c4= ' a ' ORDER by C3,C2 \g
1. Row ***************************
Id:1
Select_type:simple
Table:t6
Type:ref
Possible_keys:c1
Key:c1
Key_len:3
Ref:const
Rows:2
Extra:using where; Using Filesort
1 row in Set (0.00 sec)

The process first hit the C1, and then in order by C3,C2 when the use of C3 to do the sort, so that the joint index is broken. The using filesort indicates that no multiple-column indexes are used, but rather that the in-file ordering is done.

3.

Explain select * from T6 where c1= ' a ' and c4= ' a ' ORDER by C2,C3 \g
1. Row ***************************
Id:1
Select_type:simple
Table:t6
Type:ref
Possible_keys:c1
Key:c1
Key_len:3
Ref:const
Rows:2
Extra:using where
1 row in Set (0.00 sec)

Conversely hit C1 index, first with C2 sort, then C3 to sort, respectively hit, and finally c4= ' a ' do where query, use to multi-column index.

MySQL Multi-column index