|
|
Explain in 10.11
|
|
+------+-----------------+--------------------+--------+------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+--------------------------------------+-------+-----------------------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+--------------------+--------+------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+--------------------------------------+-------+-----------------------------------------------------------+
|
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 40802 | |
|
| 4 | DERIVED | test_town | index | PRIMARY,test_town_id_IDX | PRIMARY | 4 | NULL | 20401 | Using index; Using temporary; Using filesort |
|
| 4 | DERIVED | test_opened_ticket | ref | PRIMARY,fk_test_opened_ticket_town1,fk_test_opened_ticket_country1_idx,test_opened_ticket_test_town_id_IDX | test_opened_ticket_test_town_id_IDX | 10 | test.test_town.id,const | 1 | |
|
| 4 | DERIVED | test_ticket | eq_ref | PRIMARY,test_ticket_id_IDX | PRIMARY | 4 | test.test_opened_ticket.id | 1 | Using index |
|
| 4 | DERIVED | <derived3> | ref | key0 | key0 | 4 | test.test_town.id | 2 | Using where |
|
| 3 | LATERAL DERIVED | test_town | eq_ref | PRIMARY,test_town_id_IDX | PRIMARY | 4 | test.test_opened_ticket.test_town_id | 1 | Using where; Using index; Using temporary; Using filesort |
|
| 3 | LATERAL DERIVED | test_opened_ticket | ref | PRIMARY,fk_test_opened_ticket_town1,fk_test_opened_ticket_country1_idx,test_opened_ticket_test_town_id_IDX | test_opened_ticket_test_town_id_IDX | 10 | test.test_town.id,const | 1 | |
|
| 3 | LATERAL DERIVED | test_ticket | eq_ref | PRIMARY,test_ticket_id_IDX | PRIMARY | 4 | test.test_opened_ticket.id | 1 | Using index |
|
+------+-----------------+--------------------+--------+------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+--------------------------------------+-------+-----------------------------------------------------------+
|
Time to run the query: 1 min 32.097
|
Explain in my current 11.0 tree
|
|
+------+-----------------+--------------------+--------+------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+----------------------------+-------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-----------------+--------------------+--------+------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+----------------------------+-------+----------------------------------------------+
|
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 20401 | |
|
| 4 | DERIVED | test_town | index | PRIMARY,test_town_id_IDX | test_town_id_IDX | 4 | NULL | 20401 | Using index; Using temporary; Using filesort |
|
| 4 | DERIVED | <derived3> | ref | key0 | key0 | 4 | test.test_town.id | 1 | |
|
| 4 | DERIVED | test_opened_ticket | ref | PRIMARY,fk_test_opened_ticket_town1,fk_test_opened_ticket_country1_idx,test_opened_ticket_test_town_id_IDX | test_opened_ticket_test_town_id_IDX | 10 | test.test_town.id,const | 1 | Using where |
|
| 4 | DERIVED | test_ticket | ref | PRIMARY,test_ticket_id_IDX | test_ticket_id_IDX | 4 | test.test_opened_ticket.id | 1 | Using index |
|
| 3 | LATERAL DERIVED | test_town | eq_ref | PRIMARY,test_town_id_IDX | PRIMARY | 4 | test.test_town.id | 1 | Using where; Using temporary; Using filesort |
|
| 3 | LATERAL DERIVED | test_opened_ticket | ref | PRIMARY,fk_test_opened_ticket_town1,fk_test_opened_ticket_country1_idx,test_opened_ticket_test_town_id_IDX | test_opened_ticket_test_town_id_IDX | 10 | test.test_town.id,const | 1 | |
|
| 3 | LATERAL DERIVED | test_ticket | ref | PRIMARY,test_ticket_id_IDX | test_ticket_id_IDX | 4 | test.test_opened_ticket.id | 1 | Using index |
|
+------+-----------------+--------------------+--------+------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+----------------------------+-------+----------------------------------------------+
|
Time to run the query: 4.1 seconds
The reason for having eq_ref in some cases was because the test case had:
CREATE INDEX test_town_id_IDX USING BTREE ON test_town (id);
Which creates a non unique index on something that is unique.
The optimizer threats this as a non unique index and using it will
use 'ref'. Can be fixed by adding UNIQUE to the above definition.
However the execution time of the query will not change notable.
The above tree is the same as bb-11.0, but with one additonal bug fix
for eq_ref cost estimate):
I will do some additional testing when I am finished with the eq_ref
work and have pushed into bb-11.0, sometime next week.
In the mean time, what would help to understand the original query (that
was said to take < 1 second), if someone could add to Jira the output from
analayze format=json 'QUERY'. This would give us an idea of the data
data distribution for the query and would allow us a more accurate test case.
|
test.sql