Dyego, http://www.mysql.com/doc/en/JOIN.html
LEFT JOIN means an 'outer join'. I think it does NOT force the join order. STRAIGHT JOIN forces the join order. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html > ----- Alkuperäinen viesti ----- > Lähettäjä: "Dyego Souza Dantas Leal" <[EMAIL PROTECTED]> > Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]> > Lähetetty: Tuesday, March 02, 2004 2:39 PM > Aihe: InnoDB or MySQL error ? > > > > > > The script of the database is attached. > > > > 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_Anot > aProp,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_Anot > aProp,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: [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
