Description:
The script of database is uploaded to:
support.mysql.com/pub/mysql/secret
The name is sqlreg_italo.sql.gz
Hi,
I would like to explain what is going on with the straight_join
when it is used with my database.
Suppose the command bellow:
explain select table1 left join table2 on A=B left join table3 on
C=D where ID = 1;
When it is executed on one of our servers, mySql uses the right
order for the search. It indicates the order Table1, table2 and
table3, showing the indexes for each one.
When it is executed on another of our servers, with the same
database, but with another data, mySql indicates a non-optimized
order for the search, and do not use the appropriated indexes,
using sequential search. It can indicate, for example, table3,
table1 and table2.
To solve it I tried to modify the command, to use the
straight_join, that is intended to force mySql to use the order I
used to write the command:
explain select STRAIGHT_JOIN table1 left join table2 on A=B
left join table3 on C=D where ID = 1;
But it got no success is this case. I'd already used it with
success many times, but using it in this specific database
and in this specific situation, the Straight_Join didn't work.
I think it would be great if you could do something, because it can
be a mySql bug, or innoDb bug instead. Because this, I sent to you
an e-mail with the exact command I used and with the database I
use. With that you will be able to reproduce the bug, as I could
before sending it to you.
How to repeat:
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...
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 | |
+--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+-------+---------------------------------+
Suggested fix:
I ran the ANALYZE TABLE but it didn't work.
Description: The script of database is uploaded to: support.mysql.com/pub/mysql/secret The name is sqlreg_italo.sql.gz Hi, I would like to explain what is going on with the straight_join when it is used with my database. Suppose the command bellow: explain select table1 left join table2 on A=B left join table3 on C=D where ID = 1; When it is executed on one of our servers, mySql uses the right order for the search. It indicates the order Table1, table2 and table3, showing the indexes for each one. When it is executed on another of our servers, with the same database, but with another data, mySql indicates a non-optimized order for the search, and do not use the appropriated indexes, using sequential search. It can indicate, for example, table3, table1 and table2. To solve it I tried to modify the command, to use the straight_join, that is intended to force mySql to use the order I used to write the command: explain select STRAIGHT_JOIN table1 left join table2 on A=B left join table3 on C=D where ID = 1; But it got no success is this case. I'd already used it with success many times, but using it in this specific database and in this specific situation, the Straight_Join didn't work. I think it would be great if you could do something, because it can be a mySql bug, or innoDb bug instead. Because this, I sent to you an e-mail with the exact command I used and with the database I use. With that you will be able to reproduce the bug, as I could before sending it to you. How to repeat: 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... 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 | | +--------------+--------+----------------------------------------------------------------------------------------------------------+-------------------------+---------+----------------------+-------+---------------------------------+ Suggested fix: I ran the ANALYZE TABLE but it didn't work.