MySQL Bugs: #72815: UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required

Bug #72815	UPDATE ... WHERE ... ORDER BY... always does a filesort even if not required
Submitted:	30 May 2014 11:06	Modified:	30 May 2014 11:07
Reporter:	Umesh Shastry	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	5.1, 5.5, 5.6, 5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
It seems that Bug #36569 is partially fixed and still reproducible with the latest versions of 5.5/5.6/5.7. If you see the test case results, when UPDATE ... WHERE ... ORDER BY... used against Innodb tables then it always does a filesort, but this seems to be fixed for MyISAM tables after #36569 fix.

How to repeat:
Please use the test-case provided as part of Bug #36569

Suggested fix:
Bypass doing the filesort when it is not required to do so or document this behavior if this gets complex when changing PK in innodb and may needs to reshuffle etc

// 5.6.20, 5.7.5, 5.5.38 - All versions affected when using Innodb

## MyISAM 

mysql> CREATE TABLE update_test (a INT AUTO_INCREMENT PRIMARY KEY, b INT, c INT, INDEX (b,c))engine=myisam;
INSERT INTO update_test (b, c) VALUES (1, 1), (1, 2), (1,3), (2, 2), (2, 3), (2,4), (2, 5), (5, 5), (6,6), (7,7);

Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO update_test (b, c) VALUES (1, 1), (1, 2), (1,3), (2, 2), (2, 3), (2,4), (2, 5), (5, 5), (6,6), (7,7);
Query OK, 10 rows affected (0.00 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql>
mysql> -- no filesort
mysql> SELECT * FROM update_test WHERE b = 1 ORDER BY c LIMIT 2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 |
| 2 |    1 |    2 |
+---+------+------+
2 rows in set (0.00 sec)

mysql> SHOW SESSION STATUS LIKE 'Sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 0     |
| Sort_scan         | 0     |
+-------------------+-------+
4 rows in set (0.00 sec)

mysql>
mysql> -- does an unneeded filesort
mysql> UPDATE update_test SET a = a + 10 WHERE b = 1 ORDER BY c LIMIT 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SHOW SESSION STATUS LIKE 'Sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 0     |
| Sort_scan         | 0     |
+-------------------+-------+
4 rows in set (0.00 sec)

## InnoDB

mysql> CREATE TABLE update_test (a INT AUTO_INCREMENT PRIMARY KEY, b INT, c INT, INDEX (b,c))engine=innodb;
Query OK, 0 rows affected (0.37 sec)

mysql> INSERT INTO update_test (b, c) VALUES (1, 1), (1, 2), (1,3), (2, 2), (2, 3), (2,4), (2, 5), (5, 5), (6,6), (7,7);
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql>
mysql> -- no filesort
mysql> SELECT * FROM update_test WHERE b = 1 ORDER BY c LIMIT 2;
+---+------+------+
| a | b    | c    |
+---+------+------+
| 1 |    1 |    1 |
| 2 |    1 |    2 |
+---+------+------+
2 rows in set (0.00 sec)

mysql> SHOW SESSION STATUS LIKE 'Sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 0     |
| Sort_rows         | 0     |
| Sort_scan         | 0     |
+-------------------+-------+
4 rows in set (0.00 sec)

mysql>
mysql> -- does an unneeded filesort
mysql> UPDATE update_test SET a = a + 10 WHERE b = 1 ORDER BY c LIMIT 2;
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SHOW SESSION STATUS LIKE 'Sort%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Sort_merge_passes | 0     |
| Sort_range        | 1     |
| Sort_rows         | 2     |
| Sort_scan         | 0     |
+-------------------+-------+
4 rows in set (0.00 sec)