Bug #26915 Slow quering due to optimizer choosing incorrect index utilisation
Submitted: 7 Mar 2007 11:54 Modified: 21 Apr 2007 14:04
Reporter: Jacques Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S5 (Performance)
Version:5.0.27 OS:Linux (Redhat Linux RHEL4)
Assigned to: CPU Architecture:Any
Tags: mysql innodb merge index intercept filesort

[7 Mar 2007 11:54] Jacques
Description:
When doing a SELECT against a table using only one WHERE clause entry, it takes +/- 0.25 secs to run the query.  As soon as you have a second where clause it takes +/- 4 seconds.

How to repeat:
Run the query against database with two where clauses.

Suggested fix:
Don't automattically assume that an index merge is going to be the best plan.
[7 Mar 2007 13:29] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of SHOW TABLE STATUS for the table involved, and the results of:

select count(*) from t1 where c1 = 1;
select count(*) from t1 where с2 = 2;

(substitute your real table name and column names). Filesort is used because index on id column (primary key) is not used by optimizer.
[7 Mar 2007 14:59] Valeriy Kravchuk
Please, repeat the query as you planned and send the results. Have you ever run ANALYZE TABLE for the table used in query?
[21 Mar 2007 14:04] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.0.37, and run ANALYZE TABLE before the quesry. Inform about the results.
[21 Apr 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".