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