How to use MySQL multi-column index
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.
