Successful use of index in mysql fuzzy query? Shouldn't the key attribute be null?

Question

I created a table test_demo,and added 5000+ datas

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `tb_test`;
CREATE TABLE `tb_online_disk_mgmt` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `node_name` varchar(512) NOT NULL COMMENT '节点名称',
  `node_level` tinyint(4) NOT NULL COMMENT '节点所在层',
  `area_code` int(11) NOT NULL COMMENT '区域代码',
  PRIMARY KEY (`id`),
  KEY `area_level_name` (`area_code`,`node_level`,`node_name`(255)) USING BTREE,
  KEY `area_name` (`area_code`,`node_name`(255))

)

Fuzzy query will not use the index, will perform a full table query.But in mysql5.5 i try.Through the result of the picture like "%xxx%" doesn't use index,and searched all datas.But the key in explain result show the key_name area_level_name, why this happened?

Your question does not show much research, and a five minute search of the site would have turned up a lot of help. — Tim Biegeleisen, Mar 15 '18 at 08:22
Thats simple: the index `area_name` indexes the column `node_name`, and a `LIKE` condition that has a wildcard at the end is able to use that index (it was built up using the first 255 chars of that column). The other query can not make use of that index, it has to perform a full table scan to find matching rows. — Nico Haase, Mar 15 '18 at 08:23
Thanks for your answer Nico Haase. The second sql don't use index is right ,I konw this. But I use EXPLAIN in mysql why the attribute key's value is ```area_level_name```. Shouldn't it be null?? — Amos Sun, Mar 15 '18 at 08:33
The question isn't duplicate~~~I searched answers for a long time on net.Other answers tell us how to use index when we do fuzzy query in mysql.No response is ok for my question.... — Amos Sun, Mar 15 '18 at 08:46

score 0 · Answer 1 · answered Mar 16 '18 at 15:51

0

A leading wildcard (%) prevents use of node_name in any index). However, it could use any index beginning with area_code, thereby limited the tests to about 8274 rows. (I cannot explain why it used area_level_name instead of `area_name -- it usually picks the 'smaller' index.)

"Prefix indexing" (INDEX ... name(255) ...) is rarely useful; you found one of the cases where it can be used (LIKE "me%").

Successful use of index in mysql fuzzy query? Shouldn't the key attribute be null?

1 Answers1