MySQL Bugs: #17711: ORDER BY in DELETE forces filesort, even when applicable indexes exit

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	CPU Architecture:	Any

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...

clarification: @a in select returns 114 as well

verified on: 4.0.26, 5.1.8-bk 20050221 build

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.

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.

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.

Pushed to 4.1.23, 5.0.36, 5.1.16-beta main trees.

Noted in 4.1.23, 5.0.36, 5.1.16 changelogs.