MySQL Bugs: #12915: UPDATE/DELETE with ORDER BY, index, LIMIT not optimized well

Bug #12915	UPDATE/DELETE with ORDER BY, index, LIMIT not optimized well
Submitted:	31 Aug 2005 16:40	Modified:	20 Oct 2005 3:18
Reporter:	Dean Ellis	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	4.0 forward	OS:
Assigned to:	Sergey Petrunya	CPU Architecture:	Any

Description:
UPDATE/DELETE statements with ORDER BY and LIMIT are not optimized well, such that they unnecessarily scan more rows than needed rather than stopping early (as with SELECT ... LIMIT).

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 ( a INT, INDEX (a) );
INSERT INTO t1 VALUES (0),(0),(0),(0),(0);

FLUSH STATUS;
SELECT a FROM t1 ORDER BY a LIMIT 1;
SHOW STATUS LIKE 'handler_read%';

FLUSH STATUS;
UPDATE t1 SET a=UNIX_TIMESTAMP() LIMIT 1;
SHOW STATUS LIKE 'handler_read%';
FLUSH STATUS;
UPDATE t1 SET a=UNIX_TIMESTAMP() ORDER BY a LIMIT 1;
SHOW STATUS LIKE 'handler_read%';

FLUSH STATUS;
DELETE FROM t1 LIMIT 1;
SHOW STATUS LIKE 'handler_read%';
FLUSH STATUS;
DELETE FROM t1 ORDER BY a LIMIT 1;
SHOW STATUS LIKE 'handler_read%';

Suggested fix:
Optimize index usage + LIMIT in UPDATE/DELETE statements similar to SELECT statements.

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/internals/29715

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/internals/30564

Fix pushed into 4.1.16 and 5.0.15 trees.

Bugfix description:
When executing single table "UPDATE/DELETE ... ORDER BY ... LIMIT N" queries that have no WHERE clause, MySQL now is able to take advantage of an index that can be used to read first N rows in the ordering specified in the query.  If an index is used, query execution will read only N first records, as opposed to scanning the entire table.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Documented fix in 4.1.16 and 5.0.15 changelogs; also noted performance improvement in 5.0 Manual intro.