Bug #6420 Optimizer chooses full-text indexes based on physical order of indexes

• Submitted: 4 Nov 2004 2:12
• Modified: 5 Jan 2005 16:50
• Reporter: Andrew Yee
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.18
• OS: Linux (Red Hat 7.2 & AS 3.0)
• CPU Architecture: Any

[4 Nov 2004 2:12] Andrew Yee

Description:
If the MySQL optimizer were to select among several possible single-column full-text indexes, it would select the index created first and disregard cardinality.  I verify the order of indexes using the 'show keys from ...' and 'show create table ...' MySQL commands.  This behavior would result in poor query execution times due to selection of the less optimal index. 

How to repeat:
Create a MyISAM table with multiple single-column full-text indexes.  Create a query that will consider 2 full-text indexes 'ft_best' and 'ft_less'.  Design the table so that index 'ft_best' has the better cardinality.   

Now for the index ordering >> Create the less desirable full-text index 'ft_less' before creating 'ft_best'.  Run your query and note in the explain plan how the optimizer selects index 'ft_less' when presented with both 'ft_best' and 'ft_less' as possible keys.  Drop and recreate the full-text indexes but now having 'ft_best' created before 'ft_less'.  Rerun your query and notice in your explain plan how the optimizer now selects the 'ft_best' index.

Suggested fix:
Cardinality should influence the index selection by the MySQL optimizer and not the physical order of the indexes.

[5 Jan 2005 16:50] Aleksey Kishkin

Actually it's a feature request. It's a good idea for optimizer and I'll pass it to developer who implements fulltext search, but now it works properly ( I mean it returns proper results) and thus, cannot be described as a bug.