Bad order by performance (5.7.12 under Aurora)
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