Bug #28866 Optimizer doesn't choose best plan for execution

• Submitted: 4 Jun 2007 7:29
• Modified: 17 Aug 2007 23:07
• Reporter: Sveta Smirnova
• Status: Won't fix
• Category: MySQL Server: Optimizer
• Severity: S5 (Performance)
• Version: 4.1, 5.0, 5.1
• OS: Any (Linux)
• Assigned to: Sergey Petrunya
• CPU Architecture: Any
• Tags: bfsm_2007_06_21

[4 Jun 2007 7:29] Sveta Smirnova

Description:
Optimizer uses intersect instead of multi-key then multi-key has been specified.

How to repeat:
Load attached dump and run following queries:

select count(*) from ma_trans2 WHERE c_id = 302226 AND mailing_id = 30 and type = 2;
select count(*) from ma_trans2 force index (`ma_trans$c_id$mailing_id`) WHERE c_id = 302226 AND mailing_id = 30 and type = 2;

Altough explain shows less estimate rows count, second statement executes a bit faster.

[17 Aug 2007 23:07] Igor Babaev

- This is not a bug: our optimizer (as any other) does not guarantee the choice of the fastest execution plan in all cases.
- Non-uniform distribution or strong correlation between columns can mislead the optimizer. As a result it selects not the fastest plan.

I move the case to 'Won't fix'.