Re: optimizer bug in the index used by mysql/Innodb in the search

[Alexander Keremidarski]

Hello,

[EMAIL PROTECTED] wrote:

 Description:
     Hello,
     I am working with mysql/InnoDb 4.0.11 under Windows 2000.

     I have the following table:    
<cut>

   Time: 0.20 secs.
   Explain:         
   table   type  possible_keys       key        key_len  ref   rows  Extra
   GIROS   range PRIMARY,TipoFeVCod  TipoFeVCod    1     NULL  9417  Using where; Using index; Using filesort

   I changed the table type to MyIsam (alter table giros type=MyIsam),
   and I ran the above query:
   Time:0.02 secs       
   Explain:
   table   type  possible_keys       key        key_len  ref   rows   Extra
   GIROS   range PRIMARY,TipoFeVCod  PRIMARY       16    NULL 19472   Using where; Using index

   So, MySQL/MyIsam uses the right index (the primary index) while MySQL/InnoDB 
uses
   a wrong index (TipoFeVCod).
From EXPLAIN result output you can do only opposite conclusion:

With InnoDB MySQL chooses to use Index TipoFeVCod for which it expects to match 
9417 rows. This is about twice less rows than expected with PRIMARY key in second 
explain 19472

<cut>

How-To-Repeat:
   Select ... from giros ...
Fix:
Sorry but this is not Repeatable Test Case.

Optimizer issues highly depend on your real data.

--
 MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
 For technical support contracts, visit https://order.mysql.com/?ref=msal
    __  ___     ___ ____  __
   /  |/  /_ __/ __/ __ \/ /    Mr. Alexander Keremidarski <[EMAIL PROTECTED]>
  / /|_/ / // /\ \/ /_/ / /__   MySQL AB, Full-Time Developer
 /_/  /_/\_, /___/\___\_\___/   Sofia, Bulgaria
         <___/  www.mysql.com


---------------------------------------------------------------------
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/           (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php