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:
None 
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 1:18] Arjen Lentz
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.
[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.