| Bug #28189 | optimizer issue on Innodb tables | ||
|---|---|---|---|
| Submitted: | 2 May 2007 1:18 | Modified: | 15 May 2007 2:51 |
| Reporter: | Arjen Lentz | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.0.40, 5.1.18 | OS: | Any |
| Assigned to: | Igor Babaev | CPU Architecture: | Any |
| Tags: | bfsm_2007_05_03, INDEX, innodb, Optimizer | ||
[2 May 2007 11:11]
Sergey Petrunya
Range analyzer makes the correct choice of using incidents$status_id$asaid with E(#rows)=2. The problem seems to be within the best_access_path() function.
[10 May 2007 7:04]
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/26421 ChangeSet@1.2481, 2007-05-10 00:06:24-07:00, igor@olga.mysql.com +3 -0 Fixed bug #28189: in some rare cases optimizer preferred a more expensive ref access to a less expensive range access. This occurred only with InnoDB tables.
[13 May 2007 6:16]
Bugs System
Pushed into 5.1.19-beta
[13 May 2007 6:19]
Bugs System
Pushed into 5.0.42
[15 May 2007 2:51]
Paul DuBois
Noted in 5.0.42, 5.1.19 changelogs.
Description: The optimizer appears to be incorrectly not using a compound index on a table and instead chooses an index that involves 1000x more row scans: (note that this is not the primary key, so it's *not* a case where the clustered pk is preferred over a secondary index scan) How to repeat: Steps to reproduce: 1) load dump file attached 2) run this explain: explain select count(*) from incidents2 where status_id IN (141,104) and assgn_acct_id IN (725); notice it estimates 2 rows scanned and using incidents$status_id$asaid index *************************** 1. row *************************** id: 1 select_type: SIMPLE table: incidents2 type: range possible_keys: incidents$status_id$asaid key: incidents$status_id$asaid key_len: 7 ref: NULL rows: 2 Extra: Using where; Using index 3) alter table to innodb: alter table incidents2 engine=innodb; 4) run the explain again: explain select count(*) from incidents2 where status_id IN (141,104) and assgn_acct_id IN (725); notice it estimates thousands of rows scanned and using incidents$assgn_acct_id index *************************** 1. row *************************** id: 1 select_type: SIMPLE table: incidents2 type: ref possible_keys: incidents$assgn_acct_id,incidents$status_id$asaid key: incidents$assgn_acct_id key_len: 5 ref: const rows: 4792 Extra: Using where Suggested fix: Please fix the optimizer so both storange engines have the same behavior and choose the correct index.