MySQL Bugs: #22393: Optimizer choose non-optimal index

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

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

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)

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.

Pushed in 5.1.12

Noted in 5.1.12 changelog.

The optimizer could make an incorrect index choice for indexes with a
skewed key distribution.

Bug #20994 is marked a duplicate of this bug.