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.
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.063 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
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)