Thanks, I had seen that but I don't have a lot of flexibility for adding database specific extensions on a query by query basis.
-----Original Message----- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 2:30 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: RE: Glitch in Query Optimizer 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]
