MySQL Bugs: #91206: can't use full key

Bug #91206	can't use full key_len of the index
Submitted:	11 Jun 2018 8:28	Modified:	22 Jun 2018 11:59
Reporter:	w yj	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.7.22	OS:	CentOS (6.3)
Assigned to:		CPU Architecture:	x86

Description:
CREATE TABLE `update_list_mms` (
  `id` bigint(19) NOT NULL AUTO_INCREMENT ,
  `flag` varchar(8) DEFAULT NULL ,
   `data_sign` varchar(32) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_flag_id` (`flag`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=620486061 DEFAULT CHARSET=utf8

desc select id, data_sign, flag from update_list_wms  where id >586380999 and flag =  'mms' order by id asc limit 200;

+----+-------------+-------------------------+------------+------+---------------------+-------------+---------+-------+----------+----------+-----------------------+
| id | select_type | table                   | partitions | type | possible_keys       | key         | key_len | ref   | rows     | filtered | Extra                 |
+----+-------------+-------------------------+------------+------+---------------------+-------------+---------+-------+----------+----------+-----------------------+
|  1 | SIMPLE      | update_list_mms | NULL       | ref  | PRIMARY,idx_flag_id | idx_flag_id | 27      | const | 24208166 |    50.00 | Using index condition |
+----+-------------+-------------------------+------------+------+---------------------+-------------+---------+-------+----------+----------+-----------------------+

it uses key_len=27

it runs much slower than using key_len=35 (including id)

if I use force index

+----+-------------+-------------------------+------------+-------+---------------+-------------+---------+------+----------+----------+-----------------------+
| id | select_type | table                   | partitions | type  | possible_keys | key         | key_len | ref  | rows     | filtered | Extra                 |
+----+-------------+-------------------------+------------+-------+---------------+-------------+---------+------+----------+----------+-----------------------+
|  1 | SIMPLE      | update_list_wms | NULL       | range | idx_flag_id   | idx_flag_id | 35      | NULL | 22835966 |   100.00 | Using index condition |
+----+-------------+-------------------------+------------+-------+---------------+-------------+---------+------+----------+----------+-----------------------+

key_len=35,it will run much faster

why the sql without hint can't use full key_len?

How to repeat:
such as above

Suggested fix:
use full key_len

Hi,

First of all, could you please try changing WHERE clause from:

 id > 586380999 and flag =  'mms'

into:

flag =  'mms' and id > 586380999

without using FORCE INDEX and let us know the result.

If the result is the same, then we need  data from that table or part of those data, so that we could repeat the problem and check whether this is fixed or not in some later version / release.

Thanks in advance.

you can use data in sql file to reproduce the problem ,in final 'desc', if flag change 'wms' to 'mms',then no rows will match,
but it can use full ken_len

desc select id,data_sign,flag from update_list_mms use index (idx_flag_id) where id>586380999 and flag='wms'    order by id asc limit 100;
+----+-------------+-----------------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table           | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | update_list_mms | NULL       | ref  | idx_flag_id   | idx_flag_id | 27      | const | 1372 |    33.33 | Using index condition |
+----+-------------+-----------------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

desc select id,data_sign,flag from update_list_mms force index (idx_flag_id) where id>586380999 and flag='wms'    order by id asc limit 100;
+----+-------------+-----------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table           | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | update_list_mms | NULL       | range | idx_flag_id   | idx_flag_id | 35      | NULL |  814 |   100.00 | Using index condition |
+----+-------------+-----------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

desc select id,data_sign,flag from update_list_mms  where id>586380999 and flag='wms'    order by id asc limit 100;
+----+-------------+-----------------+------------+------+---------------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table           | partitions | type | possible_keys       | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------------+------------+------+---------------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | update_list_mms | NULL       | ref  | PRIMARY,idx_flag_id | idx_flag_id | 27      | const | 1372 |    37.14 | Using index condition |
+----+-------------+-----------------+------------+------+---------------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

desc select id,data_sign,flag from update_list_mms use index (idx_flag_id) where id>586380999 and flag='mms'    order by id asc limit 100;
+----+-------------+-----------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table           | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | update_list_mms | NULL       | range | idx_flag_id   | idx_flag_id | 35      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

Hi,

I got this with 8.0.11:

+----+-------------+-----------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table           | partitions | type  | possible_keys       | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | update_list_mms | NULL       | range | PRIMARY,idx_flag_id | idx_flag_id | 35      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+

Hence, this is fixed in 8.0.11.

I tested latest 5.7 and it is not a bug there either:

+----+-------------+-----------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table           | partitions | type  | possible_keys       | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-----------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | update_list_mms | NULL       | range | PRIMARY,idx_flag_id | idx_flag_id | 35      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-----------------+------------+-------+---------------------+-------------+---------+------+------+----------+-----------------------+

did you use this sql? pay attention the flag is ='wms' not ='mms'

desc select id,data_sign,flag from update_list_mms  where id>586380999 and flag='wms'    order by id asc limit 100;
+----+-------------+-----------------+------------+------+---------------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table           | partitions | type | possible_keys       | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-----------------+------------+------+---------------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | update_list_mms | NULL       | ref  | PRIMARY,idx_flag_id | idx_flag_id | 27      | const | 1372 |    37.14 | Using index condition |
+----+-------------+-----------------+------------+------+---------------------+-------------+---------+-------+------+----------+-----------------------+

Hi,

First of all, I have set a wrong status.

Second, I have used your original SQL, the one you used to report the bug:

desc select id, data_sign, flag from update_list_mms  where id >586380999 and flag =  'mms' order by id asc limit 200;

You reported that entire key is not used with ... flag = 'mms' ..... if the other expression changes anything, I will try it, but pray do tell me what is a difference in the percentage of rows satisfying each of those conditions.

Also, try using latin1 on that column, because multi-byte character sets may change the calculus dramatically.