Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to use the MySQL multi-column index. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Test for this issue:

Suppose a table has a federated index (c1magentin c2menc c4) then _ can only use the c1magentic c3 part of the federated index.

A where c1, and c2, and c4 > x and c3,

B where c1, and c2, and c4, order by c3

C where C1, 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,c

1. Create a test table

Click (here) to collapse or open

Mysql > show create table T1\ G

* * 1. Row *

Table: t1

Create Table: CREATE TABLE `t1` (

`c1`mediumint (8) unsigned NOT NULL DEFAULT'0'

`c2`smallint (5) unsigned NOT NULL DEFAULT'0'

`c3`int (10) unsigned NOT NULL DEFAULT'0'

`c4` int (10) unsigned NOT NULL DEFAULT'0'

`c5` mediumint (8) unsigned NOT NULL

`c6` varchar (2) DEFAULT NULL

KEY `idx_t1_c1_c2_c3_ c4` (`c1`, `c2`, `c3`, `c4`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. Option An execution plan

Click (here) to collapse or open

Mysql > explain select * from T1 where C1 100 and c2 2 and c4 > 1000 and c3 1419401948\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t1

Partitions: NULL

Type: range

Possible_keys: idx_t1_c1_c2_c3_c4

Key: idx_t1_c1_c2_c3_c4

Key_len: 13

Ref: NULL

Rows: 1

Filtered: 100.00

Extra: Using index condition

3. Option B execution plan

Click (here) to collapse or open

Mysql > explain select * from T1 where C1 100 and c2 2 and c4 14 193 17673 order by c3\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t1

Partitions: NULL

Type: ref

Possible_keys: idx_t1_c1_c2_c3_c4

Key: idx_t1_c1_c2_c3_c4

Key_len: 5

Ref: const,const

Rows: 1

Filtered: 10.00

Extra: Using index condition

4. Option C execution plan

Click (here) to collapse or open

Mysql > explain select * from T1 where C1 100 and c4 14193 17673 group by c3recoverc2\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t1

Partitions: NULL

Type: ref

Possible_keys: idx_t1_c1_c2_c3_c4

Key: idx_t1_c1_c2_c3_c4

Key_len: 3

Ref: const

Rows: 1

Filtered: 10.00

Extra: Using index condition; Using temporary; Using filesort

5. Option D execution plan

Click (here) to collapse or open

Mysql > explain select * from T1 where C1, 100 and c5, 2 order by c2, c3\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t1

Partitions: NULL

Type: ref

Possible_keys: idx_t1_c1_c2_c3_c4

Key: idx_t1_c1_c2_c3_c4

Key_len: 3

Ref: const

Rows: 1

Filtered: 10.00

Extra: Using index condition; Using where

6. Option E execution plan

Click (here) to collapse or open

Mysql > explain select * from T1 where C1 '1000 and c2' 200 and c5'2 order by c2 # c3\ G

* * 1. Row *

Id: 1

Select_type: SIMPLE

Table: t1

Partitions: NULL

Type: ref

Possible_keys: idx_t1_c1_c2_c3_c4

Key: idx_t1_c1_c2_c3_c4

Key_len: 5

Ref: const,const

Rows: 1

Filtered: 10.00

Extra: Using index condition; Using where

This is the end of this article on "how to use MySQL multi-column index". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.