List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:October 4 2006 3:28pm
Subject:Re: RE: Glitch in Query Optimizer
View as plain text  
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
>
>
Thread
Glitch in Query OptimizerRobert DiFalco3 Oct
  • RE: Glitch in Query OptimizerRobert DiFalco3 Oct
    • Re: RE: Glitch in Query OptimizerDan Buettner3 Oct
      • RE: RE: Glitch in Query OptimizerRobert DiFalco3 Oct
    • Re: Glitch in Query OptimizerChristian Hammers3 Oct
      • RE: Glitch in Query OptimizerRobert DiFalco4 Oct
        • Re: RE: Glitch in Query OptimizerDan Buettner4 Oct
  • Re: Glitch in Query OptimizerChris9 Oct
    • hello everyonealan10 Oct
      • Re: hello everyoneRolando Edwards10 Oct
    • hello everyonealan10 Oct
      • Re: hello everyonePeter Bradley10 Oct