List:General Discussion« Previous MessageNext Message »
From:Chris Date:October 9 2006 8:22am
Subject:Re: Glitch in Query Optimizer
View as plain text  
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...
Thread
Glitch in Query OptimizerRobert DiFalco3 Oct
  • RE: Glitch in Query OptimizerRobert DiFalco3 Oct
    • Re: RE: Glitch in Query OptimizerDan Buettner3 Oct
      • RE: RE: Glitch in Query OptimizerRobert DiFalco3 Oct
    • Re: Glitch in Query OptimizerChristian Hammers3 Oct
      • RE: Glitch in Query OptimizerRobert DiFalco4 Oct
        • Re: RE: Glitch in Query OptimizerDan Buettner4 Oct
  • Re: Glitch in Query OptimizerChris9 Oct
    • hello everyonealan10 Oct
      • Re: hello everyoneRolando Edwards10 Oct
    • hello everyonealan10 Oct
      • Re: hello everyonePeter Bradley10 Oct