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

[31 Aug 2005 16:40] Dean Ellis
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.
[13 Sep 2005 8:32] 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/internals/29715
[30 Sep 2005 11:35] 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/internals/30564
[19 Oct 2005 8:44] Sergey Petrunya
Fix pushed into 4.1.16 and 5.0.15 trees.
[19 Oct 2005 8:54] Sergey Petrunya
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.
[20 Oct 2005 3:18] Jon Stephens
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.