Have you tried using the USE/IGNORE/FORCE INDEX optimizer hints? http://dev.mysql.com/doc/refman/5.0/en/join.html Sometimes, MySQL's optimize just doesn't make the best choice. Somewhat rare in my experience but it happens.
HTH, Dan On 10/3/06, Robert DiFalco <[EMAIL PROTECTED]> wrote:
Scratch that, the only way to have the optimizer "choose" the correct index is to remove all compound indices that start with "NodeID" or move NodeID so that it is not the first column specified in the compound index. Ugh. Any ideas? -----Original Message----- From: Robert DiFalco [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 1:00 PM To: mysql@lists.mysql.com Subject: Glitch in Query Optimizer 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
