| Bug #74049 | Update query use filesort instead of index | ||
|---|---|---|---|
| Submitted: | 24 Sep 2014 8:01 | Modified: | 24 Sep 2014 9:59 |
| Reporter: | Matzz - | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.6.19-67.0, 5.6.22 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | filesort, Optimizer, UPDATE | ||
[24 Sep 2014 8:01]
Matzz -
[24 Sep 2014 9:59]
MySQL Verification Team
Hello Matzz, Thank you for the report. Thanks, Umesh
[24 Sep 2014 10:00]
MySQL Verification Team
//
use test;
drop table if exists test;
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL,
`b` int(11) NOT NULL,
`c` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `a` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
set @id:=0;
insert into `test` values (@id:=@id+1,@id:=@id,FLOOR(RAND() * 5000),FLOOR(RAND() * 5000)),(@id:=@id+1,FLOOR(RAND() * 5000),FLOOR(RAND() * 5000),FLOOR(RAND() * 5000)),(@id:=@id+1,FLOOR(RAND() * 5000),FLOOR(RAND() * 5000),FLOOR(RAND() * 5000)),(@id:=@id+1,FLOOR(RAND() * 5000),FLOOR(RAND() * 5000),FLOOR(RAND() * 5000));
insert into `test`(`id`,`a`,`b`,`c`)
select @id:=@id+1,@id:=@id+1,FLOOR(RAND() * 5000),FLOOR(RAND() * 5000) from
`test` k1, `test` k2, `test` k3, `test` k4,`test` k5,`test` k6, `test` k7, `test` k8, `test` k9,
`test` k0,`test` ka, `test` kb, `test` kc, `test` kd limit 5000;
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.00 sec)
mysql> SELECT id FROM `test` WHERE a=1 order by b, c LIMIT 1;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)
mysql> SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 1 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.00 sec)
mysql> explain SELECT id FROM `test` WHERE a=1 order by b, c LIMIT 1;
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | test | ref | a | a | 4 | const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
1 row in set (0.00 sec)
mysql>
mysql> flush status;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.00 sec)
mysql> UPDATE test SET id=1 WHERE a=1 ORDER BY b, c LIMIT 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql> SHOW STATUS LIKE 'handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 0 |
| Handler_read_key | 2 |
| Handler_read_last | 0 |
| Handler_read_next | 1 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 1 |
| Handler_read_rnd_next | 0 |
+-----------------------+-------+
7 rows in set (0.00 sec)
mysql> explain UPDATE test SET id=1 WHERE a=1 ORDER BY b, c LIMIT 1;
+----+-------------+-------+-------+---------------+------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-----------------------------+
| 1 | SIMPLE | test | range | a | a | 4 | const | 1 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)
mysql> show variables like '%version%';
+-------------------------+---------------------------------------------------------+
| Variable_name | Value |
+-------------------------+---------------------------------------------------------+
| innodb_version | 5.6.22 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.22-enterprise-commercial-advanced-log |
| version_comment | MySQL Enterprise Server - Advanced Edition (Commercial) |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+---------------------------------------------------------+
7 rows in set (0.00 sec)
