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]
