Re: Glitch in Query Optimizer

2006-10-09 Thread Chris
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,

RE: Glitch in Query Optimizer

2006-10-04 Thread Robert DiFalco
: Glitch in Query Optimizer On Tue, Oct 03, 2006 at 01:35:01PM -0700, Robert DiFalco 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

Re: RE: Glitch in Query Optimizer

2006-10-04 Thread Dan Buettner
Message- From: Christian Hammers [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 03, 2006 2:54 PM To: Robert DiFalco Cc: mysql@lists.mysql.com Subject: Re: Glitch in Query Optimizer On Tue, Oct 03, 2006 at 01:35:01PM -0700, Robert DiFalco wrote: Scratch that, the only way to have

Glitch in Query Optimizer

2006-10-03 Thread Robert DiFalco
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

RE: Glitch in Query Optimizer

2006-10-03 Thread Robert DiFalco
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

Re: RE: Glitch in Query Optimizer

2006-10-03 Thread Dan Buettner
: 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

RE: RE: Glitch in Query Optimizer

2006-10-03 Thread Robert DiFalco
: 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

Re: Glitch in Query Optimizer

2006-10-03 Thread Christian Hammers
On Tue, Oct 03, 2006 at 01:35:01PM -0700, Robert DiFalco 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?