Bug #16590 | Optimized does not do right "const" table pre-read | ||
---|---|---|---|
Submitted: | 18 Jan 2006 0:16 | Modified: | 13 Feb 2007 19:07 |
Reporter: | Peter Zaitsev (Basic Quality Contributor) | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 4.1 | OS: | Any (all) |
Assigned to: | Georgi Kodinov | CPU Architecture: | Any |
[18 Jan 2006 0:16]
Peter Zaitsev
[11 Jan 2007 14:23]
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/17942 ChangeSet@1.2384, 2007-01-11 16:23:18+02:00, gkodinov@macbook.gmz +3 -0 BUG#16590: Optimized does not do right "const" table pre-read st_table::const_key_parts member is used in determining if certain key has a prefix that is compared to constant(s) in the query predicates. If there's such prefix the index can be used to get the data from the remaining suffix columns in sorted order. However if a field is compared to another field from a "const" table the const_key_parts is not amended. This makes the optimizer unable to detect that the key can be used and add an extra filesort. Fixed by updating the const_key_parts after reading in the "const" table.
[22 Jan 2007 10:52]
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/18525 ChangeSet@1.2384, 2007-01-22 12:51:21+02:00, gkodinov@macbook.gmz +3 -0 BUG#16590: Optimized does not do right "const" table pre-read st_table::const_key_parts member is used in determining if certain key has a prefix that is compared to constant(s) in the query predicates. If there's such prefix the index can be used to get the data from the remaining suffix columns in sorted order. However if a field is compared to another field from a "const" table the const_key_parts is not amended. This makes the optimizer unable to detect that the key can be used for sorting and adds an extra filesort. Fixed by updating const_key_parts after reading in the "const" table.
[30 Jan 2007 22:12]
Igor Babaev
The fix has been pushed to 5.1.16-beta main tree only. As this is a purely performance problem it we do not plan to fix it in 4.1/5.0
[13 Feb 2007 19:07]
Paul DuBois
Noted in 5.1.16 changelog. The optimizer used a filesort rather than a const table read in some cases when the latter was possible.