Here is my table struct
CREATE TABLE t(
c1 CHAR(1) not null,
c2 CHAR(1) not null,
c3 CHAR(1) not null,
c4 CHAR(1) not null,
c5 CHAR(1) not null
)ENGINE InnoDB CHARSET UTF8;
alter table t add index c1234(c1,c2,c3,c4);
When I use explain select * from t where c1='1' and c2 >'2' and c3='4'
, the explain result shows that the key_len
is only 6, i.e., only c1 and c2 is active.
However, after executing explain select * from t where c1='1' and c2 like '2%' and c3='4'
, the result shows that the key_len
is 9, all index is active.
I think the LIKE %
query is a range query like <
and >
, whereas, the occurrence of LIKE %
didn't influence the use of index after it.