Bug #56925 Wrong execution of UPDATE … WHERE sec_key=… ORDER BY pk DESC
Submitted: 22 Sep 2010 11:27 Modified: 2 Feb 2011 14:25
Reporter: Marko Mäkelä Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.5 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: regression

[22 Sep 2010 11:27] Marko Mäkelä
Description:
This bug triggered Bug #56716 (InnoDB locks a record without locking the table, introduced in 5.0 when fixing Bug #27197).

MySQL 5.5 is apparently using the wrong search mode when searching a secondary index column in an update that is later sorted by the primary key in descending order. MySQL 5.1 is passing a meaningful flag:

ha_innobase::index_read (…, find_flag=HA_READ_KEY_EXACT)

MySQL 5.5 is passing this:

ha_innobase::index_read (find_flag=HA_READ_PREFIX_LAST_OR_PREV)

Both are executing the search on the secondary index c1_idx.

How to repeat:
CREATE TABLE bug56716 (
  pk INT AUTO_INCREMENT PRIMARY KEY,
  c1_idx CHAR(1) DEFAULT 'y',
  c2 INT,
  INDEX c1_idx (c1_idx)
) ENGINE=InnoDB;

UPDATE bug56716 SET c2 = 0 WHERE c1_idx = 'y' ORDER BY pk DESC LIMIT 2;

Set a breakpoint on ha_innobase::index_read() and observe the find_flag.

Suggested fix:
If the WHERE and ORDER BY clauses cannot be satisfied by the same index, process the ORDER BY clause after picking the records that match the WHERE clause.
[30 Oct 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[2 Feb 2011 14:18] Marko Mäkelä
The manifestation of this bug is performance: instead of performing a key lookup, the optimizer is asking for all records smaller than key. A key lookup could take advantage of the InnoDB adaptive hash index.
[5 Feb 2011 0:15] Omer Barnir
triage: setting tag to SR55MRU (I2 performance regression, D3 not clear the extent of the regression)
[25 Mar 2011 19:52] Rick James
Since this is InnoDB, isn't the index effectively (c1_idx, pk) ?  So the comment "WHERE and ORDER BY clauses cannot be satisfied by the same index" should be irrelevant?

Perhaps it is significant that DESC is used in the ORDER BY?