Mysql multi-column Index (Multiple-Part Index)

Mysql multi-column Index (Multiple-Part Index) when Multiple columns range scan, use the in SQL code show create table 20130314t1 CREATE TABLE '3030314t1 '('id' int (11) NOT NULL, 'col1' int (11) not null, 'col2' int (11) not null, primary key ('id'), KEY 'key1' ('col1 ', 'col2') ENGINE = InnoDB default charset = utf8 where 100 million data is inserted in the table, and col1 and col2 are random numbers of less. for the following query, the SQL code select COUNT (*) from 20130314t1 where col1> 19 and col1 <30 and col2> 30 and col 2 <33; this is the range scan performed on multi-column indexes. In theory, mysql can only use a part of the index, that is, the part of col1. From the query plan, key_len is tested by 4 mysqlslap, the average time is 0.178 s to change the query to SQL code select COUNT (*) from 20130314t1 where col1 BETWEEN 20 and 29 and col2> 30 and col2 <33; this is very strange, in theory, key_len should be 4, because the 5.1 Documentation references If the operator is >,<,>=, <= ,! =, <>, BETWEEN, or LIKE, the optimizer uses it but considers no more key parts. the result key_len is 8, but the test result of mysqlslap is not much improved. It is changed to 0.156 s and now the in statement is used. Because all statements are of the int type, the statement can be changed to the SQL code select COUNT (*) from 20130314t1 where col1 in (20, 21, 22, 23, 24, 25, 26, 27, 28, 29) and col2> 30 and col2 <33 key_len or 8, but it becomes 0.005 s because col1 BETWEEN 20 and 29 is range scan (range scan), while col1 in (20, 21, 24, 25, 26, 27, 28, 29) is multi-value equal, although the results are the same, the meaning is significantly different. you can use show status like 'handler _ % '; to observe the read status of the three statements. select COUNT (*) from 20130314t1 where col1> 19 and col1 <30 and col2> 30 and col2 <33; | Handler_read_key | 1 | Handler_read_next | 99856 | select COUNT (*) from 20130314t1 where col1 BETWEEN 20 and 29 and col2> 30 and col2 <33; | Handler_read_key | 1 | Handler_read_next | 90168 | select COUNT (*) from 20130314t1 where col1 in (20, 21, 2072, 23, 24, 25, 26, 27, 28, 29) and col2> 30 and col2 <33; | Handler_read_key | 10 | Handler_read_next becomes smaller when in is used, it indicates that the number of rows scanned by index is significantly reduced, so the number of rows scanned by index is improved.