Bug #17711 ORDER BY in DELETE forces filesort, even when applicable indexes exit
Submitted: 24 Feb 2006 20:33 Modified: 8 Feb 2007 18:43
Reporter: Domas Mituzas Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:4.0, 5.1-beta OS:Linux (Linux)
Assigned to: Evgeny Potemkin

[24 Feb 2006 20:33] Domas Mituzas
Description:
When ORDER BY clause is used in DELETE statement, filesort is done, even if order specified is on indexed field (even ASC/DESC sorts on InnoDB primary key). 

How to repeat:
CREATE TABLE `jobs` (
  `a` int(11) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=InnoDB

populate with integers..

High @a value means that table was scanned to the end: 

mysql> delete from jobs where (@a:=a) order by a limit 1; select @a;
Query OK, 1 row affected (0.00 sec)

+------+
| @a   |
+------+
| 9991 |
+------+
1 row in set (0.00 sec)

Direct PK fetch: 
mysql> select * from jobs where (@a:=a) order by a limit 1;
+-----+
| a   |
+-----+
| 114 |
+-----+
1 row in set (0.00 sec)

Suggested fix:
use indexes for deletes...
[24 Feb 2006 20:35] Domas Mituzas
clarification: @a in select returns 114 as well

verified on: 4.0.26, 5.1.8-bk 20050221 build
[23 Dec 2006 16:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17354

ChangeSet@1.2584, 2006-12-23 19:53:02+03:00, evgen@moonbone.local +3 -0
  Bug#17711: DELETE isn't using index when ORDER BY, LIMIT and non-restricting
  WHERE is present.
  
  If an index is defined and WHERE clause is present but can't be used for index
  access (like WHERE @a_var:= field) DELETE always falls back to filesort.
  Even if there is an index which can be used to speedup sorting by the ORDER BY
  list.
  
  Now if there is the WHERE clause present but it can't be used to build a 
  quick select then the mysql_delete() tries to use an index like there is no
  WHERE clause at all.
[10 Jan 2007 17:26] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17870

ChangeSet@1.2584, 2007-01-10 20:26:09+03:00, evgen@moonbone.local +3 -0
  Bug#17711: DELETE doesn't using index when ORDER BY, LIMIT and non-restricting
  WHERE is present.
  
  If a DELETE statement with ORDER BY and LIMIT contains a WHERE clause
  with conditions that for sure cannot be used for index access (like in
  WHERE @var:= field) the execution always follows the filesort path.    
  It happens currently even when for the above case there is an index that
  can be used to speedup sorting by the order by list.
  
  Now if a DELETE statement with ORDER BY and LIMIT contains such WHERE
  clause conditions that cannot be used to build any quick select then
  the mysql_delete() tries to use an index like there is no WHERE clause at all.
[11 Jan 2007 13:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/17929

ChangeSet@1.2584, 2007-01-11 16:05:03+03:00, evgen@moonbone.local +3 -0
  Bug#17711: DELETE doesn't use index when ORDER BY, LIMIT and non-restricting
  WHERE is present.
  
  If a DELETE statement with ORDER BY and LIMIT contains a WHERE clause
  with conditions that for sure cannot be used for index access (like in
  WHERE @var:= field) the execution always follows the filesort path.    
  It happens currently even when for the above case there is an index that
  can be used to speedup sorting by the order by list.
  
  Now if a DELETE statement with ORDER BY and LIMIT contains such WHERE
  clause conditions that cannot be used to build any quick select then
  the mysql_delete() tries to use an index like there is no WHERE clause at all.
[28 Jan 2007 2:00] Igor Babaev
Pushed to 4.1.23, 5.0.36, 5.1.16-beta main trees.
[8 Feb 2007 18:43] Paul Dubois
Noted in 4.1.23, 5.0.36, 5.1.16 changelogs.