| 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: | |
| 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: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]
MySQL Verification Team
Hello John Cesario, Thank you for the report. Confirmed this with 5.6.25 build. Thanks, Umesh
[5 May 2015 10:33]
MySQL Verification Team
// 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]
MySQL Verification Team
// 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]
MySQL Verification Team
// 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.
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)