Robert, I can't remember which version you said you were on, but the
last few updates to the 5.0.x series have fixed some optimizer bugs.
You might check the functionality in the latest release (5.0.25).
See http://dev.mysql.com/doc/refman/5.0/en/news-5-0-x.html
for the list of 5.0 changes.
Of particular interest might be:
http://dev.mysql.com/doc/refman/5.0/en/news-5-0-25.html
Notably,
The optimizer sometimes produced an incorrect row-count estimate after
elimination of const tables. This resulted in choosing extremely
inefficient execution plans in same cases when distribution of data in
joins were skewed. (Bug#21390)
There have been other optimizer fixes in the last few months as well.
HTH,
Dan
On 10/4/06, Robert DiFalco <rdifalco@stripped> wrote:
> Anyone here know enough about how the optimizer works to explain why it is use the
> "less optimal" index in this case?
>
> -----Original Message-----
> From: Christian Hammers [mailto:ch@stripped]
> Sent: Tuesday, October 03, 2006 2:54 PM
> To: Robert DiFalco
> Cc: mysql@stripped
> 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 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?
>
> Search the docs for "FORCE INDEX ()" and "IGNORE INDEX ()" if you don't want to rely
> on MySQL's cleverness.
>
> bye,
>
> -christian-
>
> --
> Christian Hammers WESTEND GmbH | Internet-Business-Provider
> Technik CISCO Systems Partner - Authorized Reseller
> Lütticher Straße 10 Tel 0241/701333-11
> ch@stripped D-52064 Aachen Fax 0241/911879
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
