Bug #1697 DELETE ... WHERE ... ORDER BY ... wrong execution leads to very bad performance
Submitted: 29 Oct 2003 3:45 Modified: 13 Dec 2003 3:36
Reporter: Alexander Keremidarski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.16 OS:Any (all)
Assigned to: Igor Babaev CPU Architecture:Any

[29 Oct 2003 3:45] Alexander Keremidarski
Description:
When execiting queries like:

DELETE FROM table WHERE x='value' ORDER BY x;
DELETE FROM table WHERE x='value' ORDER BY x LIMIT n;

MySQL sorts underlying table instead of rows matched by WHERE claue only

In result even for queries which match zero rows such query runs very slow.

How to repeat:
fill in random table with lot of values and test:

DELETE FROM tbl WHERE x='value';
DELETE FROM tbl WHERE x='value' ORDER BY x;

Where 'value' does not exist in table

Second will be magnitude slower if there are many rows in table.
With 1M rows and KEY(x) difference is like:

 
Watch SHOW STATUS output and see how following variables increase in this particular order:

Handler_read_rnd_next
Handler_read_rnd
Sort_rows

Suggested fix:
fix mysql_delete() in sql_delete.cc so it does not use filesort() on whole table, but on matched rows only.
[13 Dec 2003 3:36] Michael Widenius
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:

This was fixed by the same patch that fixed bug 1024.

Fix will be in 4.0.17