Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
11.0(EOL)
-
None
Description
From SO question and adapted applied to 11.0 (rather than the 10.6 question that its author asked).
Query:
with my_opened_tickets as( |
select |
test_opened_ticket.test_town_id as id, |
test_opened_ticket.nb2,
|
test_opened_ticket.nb1
|
from |
test_town ,
|
test_ticket ,
|
test_opened_ticket
|
where |
test_opened_ticket.id = test_ticket.id
|
and test_opened_ticket.test_country_id = 186 |
and test_opened_ticket.test_town_id = test_town.id |
),
|
max_nb2 as( |
select |
id,
|
max(nb2) nb2 |
from |
my_opened_tickets
|
group by id |
),
|
max_by_id_nb2 as ( |
select |
max_nb2.id,
|
max_nb2.nb2,
|
max(my_opened_tickets.nb1) |
from |
my_opened_tickets ,
|
max_nb2
|
where |
my_opened_tickets.id = max_nb2.id
|
and max_nb2.nb2 = my_opened_tickets.nb2 |
group by max_nb2.id,max_nb2.nb2 |
)
|
select * from max_by_id_nb2; |
|
10.6 result in 3/4 seconds, and query plan from OP |
id|select_type|table |type |possible_keys |key |key_len|ref |rows |Extra |
|
--+-----------+------------------+------+----------------------------------------------------------------------------------------------------------+-----------------------------------+-------+------------------------------+-----+--------------------------------------------+
|
1|PRIMARY |<derived5> |ALL | | | | |20401| |
|
5|DERIVED |<derived4> |ALL | | | | |20401|Using where; Using temporary; Using filesort|
|
5|DERIVED |test_town |eq_ref|PRIMARY,test_town_id_IDX |PRIMARY |4 |max_nb2.id |1 |Using index |
|
5|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 |max_nb2.id,const |1 |Using where |
|
5|DERIVED |test_ticket |eq_ref|PRIMARY,test_ticket_id_IDX |PRIMARY |4 |test_mlp.test_opened_ticket.id|1 |Using index |
|
4|DERIVED |test_town |index |PRIMARY,test_town_id_IDX |PRIMARY |4 | |20401|Using index |
|
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_mlp.test_town.id,const |1 | |
|
4|DERIVED |test_ticket |eq_ref|PRIMARY,test_ticket_id_IDX |PRIMARY |4 |test_mlp.test_opened_ticket.id|1 |Using index |
|
|
|
11.0 test of same query/data - 14 seconds |
+------+-------------+--------------------+-------+------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+----------------------------+-------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+--------------------+-------+------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+----------------------------+-------+----------------------------------------------+
|
| 1 | PRIMARY | <derived4> | ALL | NULL | NULL | NULL | NULL | 20401 | |
|
| 4 | DERIVED | <derived3> | ALL | NULL | NULL | NULL | NULL | 20401 | Using where; Using temporary; Using filesort |
|
| 4 | DERIVED | test_town | ref | PRIMARY,test_town_id_IDX | test_town_id_IDX | 4 | max_nb2.id | 1 | Using index |
|
| 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 | max_nb2.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 | DERIVED | test_town | index | PRIMARY,test_town_id_IDX | test_town_id_IDX | 4 | NULL | 20401 | Using index |
|
| 3 | 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 | DERIVED | test_ticket | ref | PRIMARY,test_ticket_id_IDX | test_ticket_id_IDX | 4 | test.test_opened_ticket.id | 1 | Using index |
|
+------+-------------+--------------------+-------+------------------------------------------------------------------------------------------------------------+-------------------------------------+---------+----------------------------+-------+----------------------------------------------+
|
So using eq_ref instead of eq, and PK on test_town(x2) and test_ticket for an end result of getting 14 seconds vs their claimed 3/4 seconds.
Test data generating SQL attached.