Robert DiFalco wrote:
> Here's an odd one.
>
> I have a table called Elements and another table called ElementNames.
> The ElementNames table has a unique ID and a VARCHAR display name. The
> Elements table has a ElementName.ID, a node ID, a rule ID and some other
> stuff.
>
> I have an index on the NameID, NodeID, and RuleID. I have another index
> on just the NodeID and RuleID.
>
> For the following query, MySQL normally uses the NameID, NodeID, RuleID
> index.
>
> SELECT *
> FROM Elements
> JOIN Enames ON Elements.nameID = Enames.ID
> WHERE Elements.nodeID = ?
> ORDER BY Enames.displayName
> LIMIT ?, ?;
>
> However, once I have more than about 50K elements, the query switches
> over to using the index on NodeID and RuleID (which results in a very
> slow query).
>
> I can *force* it to use the correct index by adding this to the query:
>
> SELECT *
> FROM Elements
> JOIN Enames ON Elements.nameID = Enames.ID
> WHERE Elements.nodeID = ? AND Elements.nameID <> 0
> ORDER BY Enames.displayName
> LIMIT ?, ?;
>
> It is impossible for nameID to be zero so it doesn't effect the outcome
> of the query, it just forces it to use the correct index.
>
> Any idea why this is happening? Is this a bug in the query optimizer?
Do you have an index on enames.id ?
Do you have an index on enames.id and enames.displayname ?
I don't know enough about the mysql optimizer but at a (very wild) guess
it could be mysql is trying to order the results first (so it can limit
the results quickly) and then do the join after...
