MySQL Forums
Forum List  »  Performance

Bad order by performance (5.7.12 under Aurora)
Posted by: Nicola Farina
Date: November 26, 2019 03:43AM

Hi

I am new to MySql coming from Oracle background, and I'm trying to optimize this query, on a 5.7.12 db, under Aurora (Amazon) 2.03.2, innodb 5.7.12:
SELECT *
FROM _fatture_passive f
left join relations r on r.record = f.id
left join meta m on m.id = f.id
LEFT JOIN `relations` AS `_0004_` ON _0004_.record = f.id AND _0004_.table = '_fatture_passive' AND _0004_.field = 'amministratore'
LEFT JOIN `_amministratori` AS _0005_ ON _0005_.id = _0004_.value
LEFT JOIN `relations` AS `_0006_` ON `_0006_`.`record` = f.`id` AND `_0006_`.`table` = '_fatture_passive' AND `_0006_`.`field` = 'condominio'
LEFT JOIN `_condomini` AS `_0007_` ON `_0007_`.`id` = `_0006_`.`value`
LEFT JOIN `relations` AS `_0008_` ON `_0008_`.`record` = f.id AND `_0008_`.`table` = '_fatture_passive' AND `_0008_`.`field` = 'blacklist'
LEFT JOIN `_blacklist` AS `_0009_` ON `_0009_`.`id` = `_0008_`.`value`
WHERE ( (`_0005_`.`id` IS NULL) AND (`_0007_`.`id` IS NULL) AND (`_0009_`.`id` IS NULL) )
order by f.ultima_elaborazione;

This query runs in nearly 10 seconds.
Removing the order by clause the wait time drops to under 1 second.
I would like to improve response time _without_ adding an index on the order by
field ("ultima_elaborazione").
The reasons behind this choice are two:
1. first of all the order by is dynamic, user can choose the column for sort by. I don't think it would be a good choice to index every possible column
2. since the select involves lot of columns the index could not be efficient anyway.
I have used the trace feature and found this part about the sort (the final part of the plan actually):

{
"join_execution": {
"select#": 1,
"steps": [
{
"creating_tmp_table": {
"tmp_table_info": {
"table": "intermediate_tmp_table",
"row_length": 1712,
"key_length": 0,
"unique_constraint": false,
"location": "disk (InnoDB)",
"record_format": "packed"
}
}
},
{
"filesort_information": [
{
"direction": "asc",
"table": "intermediate_tmp_table",
"field": "ultima_elaborazione"
}
],
"filesort_priority_queue_optimization": {
"limit": 200,
"rows_estimate": 364,
"row_size": 10,
"memory_available": 262144,
"chosen": false,
"cause": "quicksort_is_cheaper"
},
"filesort_execution": [
],
"filesort_summary": {
"rows": 1175,
"examined_rows": 1175,
"number_of_tmp_files": 4,
"sort_buffer_size": 6552,
"sort_mode": "<sort_key, rowid>"
}
}
]
}
I see that a temp table creation is involved
Could this step be the cause of the slowness?
Are there any option to avoid this?
Are there any parameters relevant for sort efficiency?
Thanks for any tips
bye
Nicola

Options: ReplyQuote


Subject
Views
Written By
Posted
Bad order by performance (5.7.12 under Aurora)
898
November 26, 2019 03:43AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.