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 <[EMAIL PROTECTED]> 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:[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 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 [EMAIL PROTECTED] D-52064 Aachen Fax 0241/911879 -- 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]
