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:
None 
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 -
Description:
When we have where conditions and order by on indexed columns select query uses index
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+
| 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 |
+----+-------------+-------+------+---------------+------+---------+-------+------+--------------------------+

but update with same conditions and order by use filesort
+----+-------------+-------+-------+---------------+------+---------+-------+------+-----------------------------+
| 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 |
+----+-------------+-------+-------+---------------+------+---------+-------+------+-----------------------------+

How to repeat:
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

explain SELECT id FROM `test` WHERE a=1 order by b, c LIMIT 1;
explain UPDATE test SET id=1 WHERE a=1 ORDER BY b, c LIMIT 1;
[24 Sep 2014 9:59] Umesh Shastry
Hello Matzz,

Thank you for the report.

Thanks,
Umesh
[24 Sep 2014 10:00] Umesh Shastry
//
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)