Bug #22393 Optimizer choose non-optimal index
Submitted: 15 Sep 2006 14:37 Modified: 4 Oct 2006 20:25
Reporter: Victoria Reznichenko Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:4.1,5.0,5.1 OS:Any (any)
Assigned to: Sergey Petrunya CPU Architecture:Any

[15 Sep 2006 14:37] Victoria Reznichenko
Description:
MySQL choose non-optimal index for query. With FORCE INDEX() query become 18 times faster.

See more detailed description below.

How to repeat:
1. load table and data
2. run SELECTs and EXPLAIN SELECT

Table and data uploaded to our FTP server:
ftp://ftp.mysql.com/pub/mysql/secret/rightnow_optimizer_12_18.dump.gz
[16 Sep 2006 12:27] Sergey Petrunya
This bug will be fixed by this patch: http://lists.mysql.com/commits/396.
I'll need to apply it and add testcase.

The bugfix will go into 5.1 (following the "no query plan changes in GA versions" rule)
[18 Sep 2006 10: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/12121

ChangeSet@1.2289, 2006-09-18 14:49:54+04:00, sergefp@mysql.com +3 -0
  BUG#22393: Very wrong E(#rows(ref(const)) for key with skewed distribution
  - Check if we have E(#rows) for 'range' access on the smaller interval 
    on the same index. If yes, adjust the estimate.
[1 Oct 2006 9:02] Georgi Kodinov
Pushed in 5.1.12
[4 Oct 2006 20:25] Paul DuBois
Noted in 5.1.12 changelog.

The optimizer could make an incorrect index choice for indexes with a
skewed key distribution.
[19 Aug 2007 18:54] Igor Babaev
Bug #20994 is marked a duplicate of this bug.