Hi Guys! How are you! Got a concern regarding usage of order by … limit clauses, where proper index is not being selected, nor considered, by optimizer.
We have a migrated system from MariaDB to Percona server, and we are used to rely on performance optimization given by indexes on order by … limit selects, but on latest Percona Mysql version i’m not being able to properly tune them.
Here is a (simplified) example
select table_a.aux_id
from table_a JOIN table_b ON (table_a.id = table_b.table_a_id)
ORDER BY table_a.aux_id
LIMIT 0, 10;
In this SQL the explain plan is
±-----±------------±--------±-------±--------------±-----------±--------±-----------------------------±--------±---------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±-----±------------±--------±-------±--------------±-----------±--------±-----------------------------±--------±---------------------------------------------+
| 1 | SIMPLE | table_b | index | table_a_id | table_a_id | 4 | NULL | 1229671 | Using index; Using temporary; Using filesort |
| 1 | SIMPLE | table_a | eq_ref | PRIMARY | PRIMARY | 4 | testorder.table_b.table_a_id | 1 | |
±-----±------------±--------±-------±--------------±-----------±--------±-----------------------------±--------±---------------------------------------------+
10 rows in set (2.865 sec)
but when aux_id index is used to avoid filesorting, the query perform instantly.
Of course, i can manage to apply an straight_join on this example and the query use it correctly.
±-----±------------±--------±------±--------------±-----------±--------±---------------------±-----±------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±-----±------------±--------±------±--------------±-----------±--------±---------------------±-----±------------+
| 1 | SIMPLE | table_a | index | PRIMARY | aux_id | 5 | NULL | 10 | Using index |
| 1 | SIMPLE | table_b | ref | table_a_id | table_a_id | 4 | testorder.table_a.id | 1 | Using index |
±-----±------------±--------±------±--------------±-----------±--------±---------------------±-----±------------+
10 rows in set (0.001 sec)
The tables specs is as follow:
CREATE TABLE table_a (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
aux_id int(10) unsigned DEFAULT NULL,
isEditable tinyint(3) unsigned DEFAULT 1,
PRIMARY KEY (id),
KEY aux_id (aux_id)
) ENGINE=InnoDB ;
CREATE TABLE table_b (
id int(11) NOT NULL AUTO_INCREMENT,
table_a_id int(10) unsigned NOT NULL,
appUserIdCreator int(10) unsigned NOT NULL,
appUserIdLastModifier int(10) unsigned DEFAULT NULL,
creationTime datetime NOT NULL,
PRIMARY KEY (id),
KEY creationTime (creationTime),
KEY table_a_id (table_a_id),
CONSTRAINT table_b_ibfk_1 FOREIGN KEY (table_a_id) REFERENCES table_a (id)
) ENGINE=InnoDB ;
This is a really simplified example where i could write down straight_join clause, but the same behivour is on core app complex dynamic sql generation where forcing straight_join would not be optimal.
I’ve checked that optimizer_switch prefer_ordering_index is ON an also tested modifying key_compare_cost cost model on mysql.server_cost to favor index usage on order by but got no differences so far.
What is curious is that the optimizer trace on these sql queries is not considering at all the costs by this index nor the reconsidering_access_paths_for_index_ordering part.
Any clue on how to help this kind of index usage on order by limit optimization? Maybe i’m missing some specific tuning, specific statistics or something else?
I’ve uploaded this specific testcase to following link if someone would like to play with. example.dmp.gz - Google Drive
Thanks you so much in advance for any guidance.
Regards! 