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)