CODEWITHSUNDEEP
mysqlperformanceindexing
Logan Xu
Logan Xu

Reputation: 1

MySQL doesn't use my index while it declares it will in explain statement

I recently encounter a problem involving MySQL DBSM.
The Table is like this: 

 CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(60) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`sex` enum('男','女') DEFAULT NULL,
`amount` float(10,2) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_i` (`name`),
KEY `sex` (`sex`)
) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8

As is shown above ,I create a single colume index on col name
I want to perform a range query on name, and the explain statement is

mysql> explain select * from orders where name like '王%';
+----+-------------+--------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key    | key_len | ref  | rows  | filtered | Extra                            |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
|  1 | SIMPLE      | orders | NULL       | range | name_i        | name_i | 183     | NULL | 20630 |   100.00 | Using index condition; Using MRR |
+----+-------------+--------+------------+-------+---------------+--------+---------+------+-------+----------+----------------------------------+
1 row in set, 1 warning (0.10 sec)

so it should use the index name_i and finish the query in a flash(my classmate spent 0.07 sec) however , this is how it turned out:

| 4998119 | 王缝   |   27 | 男   | 159.21 |
| 4998232 | 王求葬 |   19 | 男   | 335.65 |
| 4998397 | 王倘予 |   49 | 女   | 103.39 |
| 4998482 | 王厚   |   77 | 男   | 960.69 |
| 4998703 | 王啄淋 |   73 | 女   | 458.85 |
| 4999106 | 王般埋 |   70 | 女   | 700.98 |
| 4999359 | 王胆具 |   31 | 女   | 362.83 |
| 4999510 | 王铁脾 |   31 | 女   | 973.09 |
| 4999880 | 王战万 |   59 | 女   | 127.28 |
| 4999928 | 王忆   |   42 | 女   |  72.47 |
+---------+--------+------+------+--------+
11160 rows in set (3.43 sec)

And it seems to not use the index at all, because the data is sorted by the primary key id rather than col name(besides it is too slow ,comparing to 0.07 sec).

Has anyone encountered the problem too?

Upvotes: 0

Views: 53

Answers (1)

Rick James
Rick James

Reputation: 142298

  • What percentage of the table is "Kings" (王) ? If it is more than about 20%, it will choose to do a table scan instead of use the index. (And this may actually be faster.) (Based on Comments, 0.22% of the table is Kings.)
  • EXPLAIN and the execution of the query are separate things. Although I don't remember proving this, it is possible that the EXPLAIN might say one thing, but the query would work another way.
  • Do you have 5 million rows in the table? Was the cache 'cold' when you first ran it? And it had to fetch 11,160 rows from disk? Then the second time, all was in cache, so much faster?
  • Was the table loaded in "alphabetical" (or whatever the Chinese word for that is) order? If so, there is a good chance the ids and the names are in the same order?
  • Apparently you are using utf8_general_ci COLLATION? Maybe it does not sort Chinese well. (Provide a test case; I'll do some tests.)
  • I do not understand why it mentioned MRR.
  • I, too, am baffled by "1 min 32.24sec". The ORDER BY name should have further encouraged the Optimizer to use INDEX(name). Can you turn on "Optimizer trace".

To really see whether it used the index, do this:

FLUSH STATUS;
SELECT ...;
SHOW SESSION STATUS LIKE 'Handler%';

If the big number(s) look like the number of rows in the table, then it did a table scan. If they look more like 11160, then they used the index.

Upvotes: 1

Related Questions