Bug #19334 Optimizer does not choose right index and thus query is slow.

• Submitted: 25 Apr 2006 11:44
• Modified: 5 Jan 2007 9:30
• Reporter: Oli Sennhauser
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S5 (Performance)
• Version: 5.0.23-BK, 5.0.20
• OS: Linux (linux)
• Assigned to: Martin Hansson
• CPU Architecture: Any

[25 Apr 2006 11:44] Oli Sennhauser

Description:
Optimizer does not choose right index and thus query is slow.

How to repeat:
See privat comment for data and query.

[5 Jan 2007 9:29] Martin Hansson

This query causes the optimizer to choose a bad execution plan because
statistics information is missing about the index used to access table
karte_mitglied: mnr_id. When statistics are not available, the
optimizer assumes that the result size will be 1% of the table's size,
which is 1000 times greater than the actual result in this case. So
the second best plan appears to be cheaper (because it's less than
1000 times worse than the optimal plan), which is very wrong.

MyISAM updates its index statistics only at index creation time, or
when told so explicitly by the ANALYZE TABLE statement. This is simply
how MyISAM was designed to behave, please refer to section 7.4.7 in
the MySQL manual for further details. Other storage engines may be
designed diffently - InnoDB, for instance, keeps its statistics up to
date.

In order to update the statistics for MyISAM in this
particular case you should issue

ANALYZE TABLE KARTE_MITGLIED;

The command should complete quickly. 

It may be necessary to update statistics whenever there are substantial
changes to the table by UPDATE/DELETE/INSERT statements.