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: [EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
