Bug #76933 Performance Regression in 5.6: Update does not use index
Submitted: 4 May 2015 17:26 Modified: 16 Nov 2016 15:56
Reporter: John Cesario Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.6.24, 5.6.25 OS:Linux
Assigned to: CPU Architecture:Any
Tags: regression

[4 May 2015 17:26] John Cesario
Description:
UPDATE does not use index. In 5.5 it would have

How to repeat:
mysql> EXPLAIN EXTENDED SELECT token FROM a WHERE token = X'ad'; SHOW WARNINGS;
+----+-------------+-------+------+---------------+-------+---------+-------+--------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows   | filtered | Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+----------+--------------------------+
|  1 | SIMPLE      | a     | ref  | token         | token | 768     | const | 239046 |   100.00 | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+--------+----------+--------------------------+
1 row in set, 2 warnings (0.00 sec)

+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1                                            |
| Note    | 1003 | /* select#1 */ select `test`.`a`.`token` AS `token` from `test`.`a` where (`test`.`a`.`token` = 0xad) |
+---------+------+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN EXTENDED UPDATE a SET token = NULL WHERE token = X'ad'; SHOW WARNINGS;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+----------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | a     | index | NULL          | PRIMARY | 4       | NULL | 7205983 |   100.00 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1 |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE test.a\G
*************************** 1. row ***************************
       Table: a
Create Table: CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `token` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `token` (`token`)
) ENGINE=InnoDB AUTO_INCREMENT=7225234 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
1 row in set (0.00 sec)

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.6.24    |
+-----------+
1 row in set (0.00 sec)
[4 May 2015 17:41] Morgan Tocker
Can reproduce using the following testcase:

DROP TABLE IF EXISTS a;
CREATE TABLE `a` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `token` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `token` (`token`)
) ENGINE=InnoDB AUTO_INCREMENT=7225234 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

INSERT INTO a SELECT NULL, RAND() FROM dual;
INSERT INTO a SELECT NULL, RAND() FROM a;
INSERT INTO a SELECT NULL, RAND() FROM a;
INSERT INTO a SELECT NULL, RAND() FROM a;
INSERT INTO a SELECT NULL, RAND() FROM a;

UPDATE a SET token = X'ad' ORDER BY RAND() LIMIT 2;

EXPLAIN EXTENDED SELECT token FROM a WHERE token = X'ad'; SHOW WARNINGS;
EXPLAIN EXTENDED UPDATE a SET token = NULL WHERE token = X'ad'; SHOW WARNINGS;

mysql> DROP TABLE IF EXISTS a;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `a` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `token` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `token` (`token`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=7225234 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>
mysql> INSERT INTO a SELECT NULL, RAND() FROM dual;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql> UPDATE a SET token = X'ad' ORDER BY RAND() LIMIT 2;
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 2

mysql>
mysql> EXPLAIN EXTENDED SELECT token FROM a WHERE token = X'ad'; SHOW WARNINGS;
+----+-------------+-------+------+---------------+-------+---------+-------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | a     | ref  | token         | token | 768     | const |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+------+----------+--------------------------+
1 row in set, 2 warnings (0.01 sec)

+---------+------+----------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                  |
+---------+------+----------------------------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1                                               |
| Note    | 1003 | /* select#1 */ select `test2`.`a`.`token` AS `token` from `test2`.`a` where (`test2`.`a`.`token` = 0xad) |
+---------+------+----------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN EXTENDED UPDATE a SET token = NULL WHERE token = X'ad'; SHOW WARNINGS;
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | a     | index | NULL          | PRIMARY | 4       | NULL |   16 |   100.00 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1 |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
[4 May 2015 17:44] Morgan Tocker
Behavior appears to be related to the invalid value of '\xAD'.    If I change the table to have token defined as a varbinary, an index is used:

mysql> EXPLAIN EXTENDED UPDATE a SET token = NULL WHERE token = X'ad'; SHOW WARNINGS;
+----+-------------+-------+-------+---------------+-------+---------+-------+------+----------+------------------------------+
| id | select_type | table | type  | possible_keys | key   | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+-------+---------------+-------+---------+-------+------+----------+------------------------------+
|  1 | SIMPLE      | a     | range | token         | token | 258     | const |    2 |   100.00 | Using where; Using temporary |
+----+-------------+-------+-------+---------------+-------+---------+-------+------+----------+------------------------------+
1 row in set (0.00 sec)

Empty set (0.00 sec)
[4 May 2015 17:50] Morgan Tocker
Confirming that 5.7 is not affected (requires sql_mode unset since character is invalid):

root@localhost [test] > DROP TABLE IF EXISTS a;
Query OK, 0 rows affected, 1 warning (0.02 sec)

Note (Code 1051): Unknown table 'test.a'
root@localhost [test] > set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

Warning (Code 3129): Unsetting sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be made read-only in a future release.
root@localhost [test] > CREATE TABLE `a` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `token` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `token` (`token`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=7225234 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.10 sec)

root@localhost [test] >
root@localhost [test] > INSERT INTO a SELECT NULL, RAND() FROM dual;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

root@localhost [test] > INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

root@localhost [test] > INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

root@localhost [test] > INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

root@localhost [test] > INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

root@localhost [test] >
root@localhost [test] > UPDATE a SET token = X'ad' ORDER BY RAND() LIMIT 2;
Query OK, 2 rows affected, 2 warnings (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 2

Warning (Code 1366): Incorrect string value: '\xAD' for column 'token' at row 1
Warning (Code 1366): Incorrect string value: '\xAD' for column 'token' at row 2
root@localhost [test] >
root@localhost [test] > EXPLAIN EXTENDED SELECT token FROM a WHERE token = X'ad'; SHOW WARNINGS;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | a     | NULL       | ref  | token         | token | 768     | const |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
1 row in set, 5 warnings (0.00 sec)

Warning (Code 1681): 'EXTENDED' is deprecated and will be removed in a future release.
Warning (Code 1366): Incorrect string value: '\xAD' for column 'token' at row 1
Warning (Code 1366): Incorrect string value: '\xAD' for column 'token' at row 1
Warning (Code 1366): Incorrect string value: '\xAD' for column 'token' at row 1
Note (Code 1003): /* select#1 */ select `test`.`a`.`token` AS `token` from `test`.`a` where (`test`.`a`.`token` = 0xad)
+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                     |
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1                                            |
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1                                            |
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1                                            |
| Note    | 1003 | /* select#1 */ select `test`.`a`.`token` AS `token` from `test`.`a` where (`test`.`a`.`token` = 0xad) |
+---------+------+-------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

root@localhost [test] > EXPLAIN EXTENDED UPDATE a SET token = NULL WHERE token = X'ad'; SHOW WARNINGS;
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+------------------------------+
|  1 | UPDATE      | a     | NULL       | range | token         | token | 768     | const |    2 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+------------------------------+
1 row in set, 2 warnings (0.00 sec)

Warning (Code 1681): 'EXTENDED' is deprecated and will be removed in a future release.
Warning (Code 1366): Incorrect string value: '\xAD' for column 'token' at row 1
+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. |
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1        |
+---------+------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

root@localhost [test] > select version();
+--------------+
| version()    |
+--------------+
| 5.7.7-rc-log |
+--------------+
1 row in set (0.00 sec)
[4 May 2015 18:02] Morgan Tocker
Confirmed via Handler counts that 5.5 does not table scan.  So this is a regression that affects 5.6 only.
[5 May 2015 10:30] Umesh Shastry
Hello John Cesario,

Thank you for the report.
Confirmed this with 5.6.25 build.

Thanks,
Umesh
[5 May 2015 10:33] Umesh Shastry
// 5.5.45

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.5.45:  bin/mysql -uroot -p -S/tmp/mysql_ushastry.sock test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.45 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS a;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `a` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `token` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `token` (`token`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=7225234 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql>
mysql> INSERT INTO a SELECT NULL, RAND() FROM dual;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> UPDATE a SET token = X'ad' ORDER BY RAND() LIMIT 2;
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 2

mysql> EXPLAIN EXTENDED SELECT token FROM a WHERE token = X'ad'; SHOW WARNINGS;
+----+-------------+-------+------+---------------+-------+---------+-------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | a     | ref  | token         | token | 768     | const |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+------+----------+--------------------------+
1 row in set, 2 warnings (0.00 sec)

+---------+------+----------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                |
+---------+------+----------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1                             |
| Note    | 1003 | select `test`.`a`.`token` AS `token` from `test`.`a` where (`test`.`a`.`token` = 0xad) |
+---------+------+----------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
[5 May 2015 10:33] Umesh Shastry
// 5.6.25

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.6.25:  bin/mysql -uroot -p -S/tmp/mysql_ushastry.sock test
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.25-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS a;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `a` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `token` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `token` (`token`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=7225234 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql>
mysql> INSERT INTO a SELECT NULL, RAND() FROM dual;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql> UPDATE a SET token = X'ad' ORDER BY RAND() LIMIT 2;

Query OK, 2 rows affected, 2 warnings (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 2

mysql>
mysql> EXPLAIN EXTENDED SELECT token FROM a WHERE token = X'ad'; SHOW WARNINGS;
+----+-------------+-------+------+---------------+-------+---------+-------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------+---------------+-------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | a     | ref  | token         | token | 768     | const |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------+---------------+-------+---------+-------+------+----------+--------------------------+
1 row in set, 2 warnings (0.00 sec)

+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1                                            |
| Note    | 1003 | /* select#1 */ select `test`.`a`.`token` AS `token` from `test`.`a` where (`test`.`a`.`token` = 0xad) |
+---------+------+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> EXPLAIN EXTENDED UPDATE a SET token = NULL WHERE token = X'ad'; SHOW WARNINGS;
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | a     | index | NULL          | PRIMARY | 4       | NULL |   16 |   100.00 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

+---------+------+------------------------------------------------------------+
| Level   | Code | Message                                                    |
+---------+------+------------------------------------------------------------+
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1 |
+---------+------+------------------------------------------------------------+
1 row in set (0.00 sec)
[5 May 2015 10:33] Umesh Shastry
// 5.7.8

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.8:  bin/mysql -uroot -p -S/tmp/mysql_ushastry.sock  test
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.8-rc-enterprise-commercial-advanced MySQL Enterprise Server - Advanced Edition (Commercial)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS a;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `a` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `token` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `token` (`token`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=7225234 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> INSERT INTO a SELECT NULL, RAND() FROM dual;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> INSERT INTO a SELECT NULL, RAND() FROM a;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql>
mysql> UPDATE a SET token = X'ad' ORDER BY RAND() LIMIT 2;
Query OK, 2 rows affected, 2 warnings (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 2

mysql>
mysql> EXPLAIN EXTENDED SELECT token FROM a WHERE token = X'ad'; SHOW WARNINGS;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | a     | NULL       | ref  | token         | token | 768     | const |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+--------------------------+
1 row in set, 5 warnings (0.00 sec)

+---------+------+-------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                               |
+---------+------+-------------------------------------------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release.                                     |
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1                                            |
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1                                            |
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1                                            |
| Note    | 1003 | /* select#1 */ select `test`.`a`.`token` AS `token` from `test`.`a` where (`test`.`a`.`token` = 0xad) |
+---------+------+-------------------------------------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql> EXPLAIN EXTENDED UPDATE a SET token = NULL WHERE token = X'ad'; SHOW WARNINGS;
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+------------------------------+
|  1 | UPDATE      | a     | NULL       | range | token         | token | 768     | const |    2 |   100.00 | Using where; Using temporary |
+----+-------------+-------+------------+-------+---------------+-------+---------+-------+------+----------+------------------------------+
1 row in set, 2 warnings (0.00 sec)

+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1681 | 'EXTENDED' is deprecated and will be removed in a future release. |
| Warning | 1366 | Incorrect string value: '\xAD' for column 'token' at row 1        |
+---------+------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)
[16 Nov 2016 15:56] Paul Dubois
Posted by developer:
 
Noted in 5.7.17, 8.0.1 changelogs.

An invalid string value in the WHERE clause of an UPDATE statement,
caused an index scan rather than a range scan to be used. For values
not present in the index, this could be much slower. Now the
optimizer determines this to be an "impossible WHERE" condition.