Bug #72518 UPDATE ORDER BY does a filesort if updating a column in the index

• Submitted: 2 May 2014 23:42
• Modified: 30 Aug 2015 20:59
• Reporter: Harrison Fisk
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S5 (Performance)
• Version: 5.6.16
• OS: Any
• CPU Architecture: Any
• Tags: filesort, Optimizer

[2 May 2014 23:42] Harrison Fisk

Description:
If you are updating a column which is part of the index being used for the sorting, then the optimizer will always do a filesort.  When you are using a small limit with the ORDER BY, this can result in dramatically different performance and locking characteristics.

It looks like the fix for http://bugs.mysql.com/bug.php?id=36569 does not cover this case.

How to repeat:
 CREATE TABLE `t1` (
  `a` int(11) NOT NULL AUTO_INCREMENT,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `e` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`,`c`,`d`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- add some data
insert into t1 (b, c, d) values (1, 1, 1), (2, 2, 2), (2, 3, 4), (3, 3, 3), (4, 4, 4), (5, 5, 5), (5, 6, 7), (5, 7, 9);

-- does not use a filesort
explain update t1 set e = 72 where b = 2 order by c asc limit 1;

-- uses a filesort
explain update t1 set d = 72 where b = 2 order by c asc limit 1;

Suggested fix:
Don't do the filesort.

[30 Aug 2015 20:59] Paul DuBois

Noted in 5.7.9, 5.8.0 changelogs.

For UPDATE statements with ORDER BY, the optimizer could perform an
unnecessary filesort on a key that was used for scanning as well as
being updated.