mysql组合索引like的问题

[abs.征召]

建表语句 ：
CREATE TABLE `t` (
`c1` char(2) NOT NULL DEFAULT '',
`c2` char(2) NOT NULL DEFAULT '',
`c3` char(2) NOT NULL DEFAULT '',
`c4` char(2) NOT NULL DEFAULT '',
`c5` char(2) NOT NULL DEFAULT '',
KEY `test_index` (`c1`,`c2`,`c3`,`c4`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

insert into `t` (`c1`, `c2`, `c3`, `c4`, `c5`) values('1','2','3','4','5');
insert into `t` (`c1`, `c2`, `c3`, `c4`, `c5`) values('a','c','df','d','f');
insert into `t` (`c1`, `c2`, `c3`, `c4`, `c5`) values('d','x','d','d','x');
insert into `t` (`c1`, `c2`, `c3`, `c4`, `c5`) values('s','s','df','','d');


测试中发现：
EXPLAIN
SELECT * FROM t WHERE c1 = '1' AND c2 = "2" AND c3 LIKE '3%' AND c4 = '2'
这种语句 4列都可以用到索引

这是为啥呢？
我 看《高性能mysql》中说 “如果某个列是范围查询 他后面的列都无法使用索引优化”

求解~~~

[rick-he]

你可以多插入几条，最好是上万,再测试

[abs.征召]

#1 100000万条 测试还是找个效果呢

[ACMAIN_CHM]

c3 LIKE '3%' 是可以用到索引的啊。

引用

For BTREE indexes, comparison of a key part with a constant value is a range condition when using the >, <, >=, <=, BETWEEN, !=, or <> operators, or LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

[rick-he]

引用 2 楼 u014156301 的回复:

#1 100000万条 测试还是找个效果呢

c1和c2有用到索引吗？

[abs.征召]

引用 4 楼 weixin_29261291 的回复:

[quote=引用 3楼ACMAIN_CHM 的回复:]c3 LIKE '3%' 是可以用到索引的啊。

引用

For BTREE indexes, comparison of a key part with a constant value is a range condition when using the >, <, >=, <=, BETWEEN, !=, or <> operators, or LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

第四个字段也可以用到。 为啥呢[/quote]

引用 6 楼 u011575570 的回复:

[quote=引用 2 楼 u014156301 的回复:] #1 100000万条 测试还是找个效果呢

c1和c2有用到索引吗？ [/quote] 通过看这个key_len的长度 是用到了

[「已注销」]

引用 3楼ACMAIN_CHM 的回复:

c3 LIKE '3%' 是可以用到索引的啊。

引用

For BTREE indexes, comparison of a key part with a constant value is a range condition when using the >, <, >=, <=, BETWEEN, !=, or <> operators, or LIKE comparisons if the argument to LIKE is a constant string that does not start with a wildcard character.

第四个字段也可以用到。 为啥呢

[WWWWA]

c3 LIKE '3%'　　可以用到索引 c3 LIKE '%3' 无法用到

[码无边]

你要明白什么是范围查询