| 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: | |
| 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 | ||
[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.
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.