List:General Discussion« Previous MessageNext Message »
From:Chris Nolan Date:March 2 2004 12:50pm
Subject:Re: InnoDB or MySQL error ?
View as plain text  
If there's a bug in the optimiser, you'll find it's in the "higher 
levels" of the codebase. InnoDB doesn't have any SQL optimisation code 
in it, so any bugs in this area aren't in InnoDB.

Regards,

Chris

Dyego Souza Dantas Leal wrote:

>The script of database are uploaded to:
>
>support.mysql.com/pub/mysql/secret
>
>The name is sqlreg_italo.sql.gz
>
>I'am testing with MySQL 4.0.18-pro on Debian Linux Box.
>Kernel 2.4 and 2.6
>
>There is an error in MySQL optimizer !!!
>
>test:
>
>
>mysql> SET FOREIGN_KEY_CHECKS=0;
>Query OK, 0 rows affected (0.00 sec)
>
>mysql> create database sqlreg3_italo;use sqlreg3_italo;source sqlreg_italo.sql;
>Database changed
>.....
>
>Query OK, 0 rows affected (0.02 sec)
>
>mysql> SET FOREIGN_KEY_CHECKS=1;
>Query OK, 0 rows affected (0.02 sec)
>
>mysql> explain select straight_join *
>    -> from anparte as ap
>    -> left join an on an.An_Id = ap.Ap_Anotacao
>    -> left join destino_selo on ds_Destino = an.an_id
>    -> left join selo on ds_selo = se_id
>    -> left join l1 on L1_Protocolo = an.An_Protocolo
>    -> left join l2 on an.An_Livro = "2" AND L2_Id = an.An_IdLivro
>    -> left join l3 on an.An_Livro = "3" AND L3_Id = an.An_IdLivro
>    -> left join tr on an.An_Livro = "T" AND Tr_Id = an.An_IdLivro
>    -> left join lri on an.An_Livro = "I" AND lr_Id = an.An_IdLivro
>    -> 
>    -> where an.An_Livro = "2"
>    -> group by ap_Anotacao
>    -> order by an.An_Num
>    -> limit 20;
>+--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+-------+------+---------------------------------+
>| table        | type   | possible_keys                                               
>                                             | key                     | key_len | ref   |
> rows | Extra                           |
>+--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+-------+------+---------------------------------+
>| destino_selo | system | PRIMARY                                                     
>                                             | NULL                    |    NULL | NULL  | 
>   0 | const row not found             |
>| selo         | system | PRIMARY,Se_Id                                               
>                                             | NULL                    |    NULL | NULL  | 
>   0 | const row not found             |
>| l1           | system | L1_Protocolo,IL1_ProtocoloEtapa                             
>                                             | NULL                    |    NULL | NULL  | 
>   0 | const row not found             |
>| l2           | system | L2_Id                                                       
>                                             | NULL                    |    NULL | NULL  | 
>   0 | const row not found             |
>| l3           | system | L3_Id                                                       
>                                             | NULL                    |    NULL | NULL  | 
>   0 | const row not found             |
>| tr           | system | Tr_Id                                                       
>                                             | NULL                    |    NULL | NULL  | 
>   0 | const row not found             |
>| lri          | system | Lr_Id,PRIMARY_IDX2                                          
>                                             | NULL                    |    NULL | NULL  | 
>   0 | const row not found             |
>| ap           | index  |
> IAp_AnotacaoImportancia,IAp_AnotaOnusOnusCanc,IAp_AnotaCompCompCanc,IAp_AnotaProp,IAp_AnotaClassifTransf
> | IAp_AnotacaoImportancia |       8 | NULL  |    0 | Using temporary; Using filesort |
>| an           | ref    | An_Id,IAn_L4_LivroIdLvNum,Ian_LivroNumLri                   
>                                             | Ian_LivroNumLri         |       1 | const | 
>   1 | Using where                     |
>+--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+-------+------+---------------------------------+
>9 rows in set (0.01 sec)
>
>
>
>The   word  "straight_join"  make it explicit for the MySQL to use the
>indexes in the left join order... but it isn't working...
>
>Is it a MySQL opt bug or InnoDB opt bug ?
>
>The correct response of mysql is:
>
>+--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+-------+---------------------------------+
>| table        | type   | possible_keys                                               
>                                             | key                     | key_len | ref     
>             | rows  | Extra                           |
>+--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+-------+---------------------------------+
>| ap           | index  |
> IAp_AnotacaoImportancia,IAp_AnotaOnusOnusCanc,IAp_AnotaCompCompCanc,IAp_AnotaProp,IAp_AnotaClassifTransf
> | IAp_AnotacaoImportancia |       8 | NULL                 | 72821 | Using temporary;
> Using filesort |
>| an           | eq_ref | PRIMARY,IAn_L4_LivroIdLvNum,Ian_LivroNumLri                 
>                                             | PRIMARY                 |       4 |
> ap.Ap_Anotacao       |     1 | Using where                     |
>| destino_selo | ref    | PRIMARY                                                     
>                                             | PRIMARY                 |       4 | an.An_Id
>             |     1 | Using index                     |
>| selo         | eq_ref | PRIMARY,Se_Id                                               
>                                             | PRIMARY                 |       4 |
> destino_selo.Ds_Selo |     1 |                                 |
>| l1           | eq_ref | PRIMARY,IL1_ProtocoloEtapa                                  
>                                             | PRIMARY                 |       4 |
> an.An_Protocolo      |     1 |                                 |
>| l2           | eq_ref | PRIMARY                                                     
>                                             | PRIMARY                 |       4 |
> an.An_IdLivro        |     1 |                                 |
>| l3           | eq_ref | PRIMARY                                                     
>                                             | PRIMARY                 |       4 |
> an.An_IdLivro        |     1 |                                 |
>| tr           | eq_ref | PRIMARY                                                     
>                                             | PRIMARY                 |       4 |
> an.An_IdLivro        |     1 |                                 |
>| lri          | eq_ref | PRIMARY,Lr_Id                                               
>                                             | PRIMARY                 |       4 |
> an.An_IdLivro        |     1 |                                 |
>+--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+-------+---------------------------------+
>
>ps: I ran the ANALYZE TABLE but it didn't work.
>
>InnoDB,SQL,Query,MySQL,Help !!!!
>
>-------------------------------------------------------------------------
>  ++  Dyego Souza Dantas Leal   ++           Dep. Desenvolvimento   
>-------------------------------------------------------------------------
>                 E S C R I B A   I N F O R M A T I C A
>-------------------------------------------------------------------------
>The only stupid question is the unasked one (somewhere in Linux's HowTo)
>Linux registred user : #230601
>--                                        ICQ   : 1647350                            
>$ look into "my eyes"                     Phone : +55 041 2106-1212              
>look: cannot open my eyes                 Fax   : +55 041 296 -6640        
>-------------------------------------------------------------------------
>               Reply: dyego@stripped
>
>
>  
>


Thread
InnoDB or MySQL error ?Dyego Souza Dantas Leal2 Mar
  • Re: InnoDB or MySQL error ?Chris Nolan2 Mar
Re: InnoDB or MySQL error ?Heikki Tuuri2 Mar
  • Re[2]: InnoDB or MySQL error ?Dyego Souza Dantas Leal2 Mar
  • Re: Re[2]: InnoDB or MySQL error ?Heikki Tuuri2 Mar