What's reason of that LIKE query didn't disable the composite Index in MySQL

43 Views Asked by At

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.

0

There are 0 best solutions below