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...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
