Bug #30665 Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY}
Submitted: 28 Aug 2007 8:03 Modified: 13 Nov 2007 18:59
Reporter: Martin Hansson Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.1.17 OS:Any
Assigned to: Martin Hansson CPU Architecture:Any
Tags: GROUP BY, hints, ignore index

[28 Aug 2007 8:03] Martin Hansson
Description:
The optimizer hint IGNORE INDEX FOR {ORDER BY|GROUP BY} gets ignored if there is a covering index, see manual http://dev.mysql.com/doc/refman/5.1/en/index-hints.html

However, when both are used the same way, the IGNORE INDEX FOR ORDER BY unneccesarily uses filesort.

How to repeat:
CREATE TABLE t1 (a INT, b INT,
                 PRIMARY KEY (a),
                 KEY i2(a,b));
INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR GROUP BY (PRIMARY,i2) GROUP BY a\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 8
        Extra: Using index

EXPLAIN SELECT a FROM t1 IGNORE INDEX FOR ORDER BY (PRIMARY,i2) ORDER BY a\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 8
        Extra: Using index; Using filesort

Suggested fix:
This happens because the function that checks if sorting can be skipped is fed
the set of possible keys for the table, minus the ones that are in the IGNORE INDEX FOR ORDER BY clause, if any. The effect is the one above.

This should be fixed by including all keys when checking if sorting can be skipped, because non-covering keys will be rejected anyway. And if they are covering, the hint should be ignored.
[29 Aug 2007 11:19] 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/commits/33292

ChangeSet@1.2577, 2007-08-29 13:18:09+02:00, mhansson@linux-st28.site +6 -0
  Bug#30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY}
  
  The test of sort keys for ORDER BY was prohibited from considering keys
  that were mentioned in IGNORE KEYS FOR ORDER BY. This led to two 
  inconsistencies: One was that IGNORE INDEX FOR GROUP BY and 
  IGNORE INDEX FOR ORDER BY gave different plans; the latter had an 
  unneccesary filesort. The second inconsistency is that the test of sort
  keys finds no usable sort key, but one is used anyway, leading to the 
  mentioned filesort.
  
  Fixed by making the test of sort keys consider all enabled keys on the table.
  This test rejects keys that are not covering, and for covering keys the 
  hint should be ignored anyway.
[4 Sep 2007 9:48] 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/commits/33622

ChangeSet@1.2577, 2007-09-04 11:49:29+02:00, mhansson@linux-st28.site +6 -0
  Bug#30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY}
  
  The test of sort keys for ORDER BY was prohibited from considering keys
  that were mentioned in IGNORE KEYS FOR ORDER BY. This led to two 
  inconsistencies: One was that IGNORE INDEX FOR GROUP BY and 
  IGNORE INDEX FOR ORDER BY gave different plans; the latter had an 
  unneccesary filesort. The second inconsistency is that the test of sort
  keys finds no usable sort key, but one is used anyway, leading to the 
  mentioned filesort.
  
  Fixed by making the test of sort keys consider all enabled keys on the table.
  This test rejects keys that are not covering, and for covering keys the 
  hint should be ignored anyway.
[14 Sep 2007 10:11] 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/commits/34255

ChangeSet@1.2577, 2007-09-14 12:12:54+02:00, mhansson@linux-st28.site +5 -0
  Bug#30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY}
  
  The optimizer takes different execution paths during EXPLAIN than SELECT,
  this fix relates only to EXPLAIN, hence no behavior changes.
  The test of sort keys for ORDER BY was prohibited from considering keys
  that were mentioned in IGNORE KEYS FOR ORDER BY. This led to two 
  inconsistencies: One was that IGNORE INDEX FOR GROUP BY and 
  IGNORE INDEX FOR ORDER BY gave apparently different EXPLAINs; the latter 
  erroneously claimed to do filesort. The second inconsistency 
  is that the test of sort keys is called twice, finding a sort key the first
  time but not the second time, leading to the mentioned filesort.
  
  Fixed by making the test of sort keys consider all enabled 
  keys on the table. This test rejects keys that are not covering, and for 
  covering keys the hint should be ignored anyway.
[25 Sep 2007 15:45] 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/commits/34547

ChangeSet@1.2577, 2007-09-25 17:47:27+02:00, mhansson@linux-st28.site +5 -0
  Bug#30665: Inconsistent optimization of IGNORE INDEX FOR {ORDER BY|GROUP BY}
  
  The optimizer takes different execution paths during EXPLAIN than SELECT,
  this fix relates only to EXPLAIN, hence no behavior changes.
  The test of sort keys for ORDER BY was prohibited from considering keys
  that were mentioned in IGNORE KEYS FOR ORDER BY. This led to two 
  inconsistencies: One was that IGNORE INDEX FOR GROUP BY and 
  IGNORE INDEX FOR ORDER BY gave apparently different EXPLAINs; the latter 
  erroneously claimed to do filesort. The second inconsistency 
  is that the test of sort keys is called twice, finding a sort key the first
  time but not the second time, leading to the mentioned filesort.
  
  Fixed by making the test of sort keys consider all enabled 
  keys on the table. This test rejects keys that are not covering, and for 
  covering keys the hint should be ignored anyway.
[28 Sep 2007 8:15] Martin Hansson
This bug appears in 5.2 also, pushing to 5.1 and 5.2
[29 Oct 2007 8:47] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:50] Bugs System
Pushed into 6.0.4-alpha
[13 Nov 2007 18:59] Paul Dubois
Noted in 5.1.23, 6.0.4 changelogs.