I have this query that running against Maridb, when we query in ASC order, the optimizer examing less amount of records(r_rows) and completes the query in ~500 milliseconds, but when switching the order by to DESC then the same query taking more time to complete and r_rows going ~2.27 million.
Why is that? Why is ASC/DESC order impacting query performance?
Here is the SQL Query
SELECT x_nuvo_eam_scheduled_m9e_e8s0.`sys_id`
FROM (
x_nuvo_eam_scheduled_m9e_e8s x_nuvo_eam_scheduled_m9e_e8s0
LEFT JOIN x_nuvo_eam_scheduled_m10s x_nuvo_eam_scheduled_maintena1 ON x_nuvo_eam_scheduled_m9e_e8s0.`scheduled_maintenance` = x_nuvo_eam_scheduled_maintena1.`sys_id`
)
WHERE x_nuvo_eam_scheduled_m9e_e8s0.`status` = 'Pending'
AND x_nuvo_eam_scheduled_m9e_e8s0.`scheduled_date` >= '2022-02-15 06:00:00'
AND x_nuvo_eam_scheduled_maintena1.`asset` IS NULL
ORDER BY x_nuvo_eam_scheduled_m9e_e8s0.`sys_created_on` ASC
limit 0, 100
Below 2 MariaDB Analyze output that shows to the execution plan
ASC ordered Query Completes ~503 ms
--------- ------------------------------------------------------------------------------------------------------------------------
| 1 result(s):
--------- ------------------------------------------------------------------------------------------------------------------------
| ANALYZE | {
| | "query_block": {
| | "select_id": 1,
| | "r_loops": 1,
| | "r_total_time_ms": 503.93,
| | "table": {
| | "table_name": "Table_A",
| | "access_type": "index",
| | "possible_keys": ["idx1"],
| | "key": "sys_created_on",
| | "key_length": "6",
| | "used_key_parts": ["sys_created_on"],
| | "r_loops": 1,
| | "rows": 2695302,
| | "r_rows": 234328,
| | "r_total_time_ms": 476.64,
| | "filtered": 50,
| | "r_filtered": 0.1903,
| | "attached_condition": "Table_A.`status` = 'Pending' and Table_A.scheduled_date >= '2022-02-15 06:00:00'"
| | },
--------- ------------------------------------------------------------------------------------------------------------------------
DESC ASC ordered Query Completes ~9118 ms
r_rows significantly Larger as comparing to ASC.
--------- -----------------------------------------------------------------------------------------------------------------------
| 1 result(s):
--------- -----------------------------------------------------------------------------------------------------------------------
| ANALYZE | {
| | "query_block": {
| | "select_id": 1,
| | "r_loops": 1,
| | "r_total_time_ms":9118.4,
| | "table": {
| | "table_name": "Table_A",
| | "access_type": "index",
| | "possible_keys": ["idx1"],
| | "key": "sys_created_on",
| | "key_length": "6",
| | "used_key_parts": ["sys_created_on"],
| | "r_loops": 1,
| | "rows": 2695302,
| | "r_rows": 2.27e6,
| | "r_total_time_ms": 4380.1,
| | "filtered": 50,
| | "r_filtered": 70.102,
| | "attached_condition": "Table_A.`status` = 'Pending' and Table_A.scheduled_date >= '2022-02-15 06:00:00'" |
| | },
--------- -----------------------------------------------------------------------------------------------------------------------
CodePudding user response：
Index suggestions to optimize
Table Index x_nuvo_eam_scheduled_m9e_e8s ( status, scheduled_date, scheduled_maintenance, sys_created_on ) x_nuvo_eam_scheduled_m10s ( sys_id )
Then, revised without (parens) and ticks, but also making use of cleaner aliases of scheduled vs maintenance. The first table having a proper index to optimize on the WHERE and JOIN criteria will help. But also having the created on to complete the covering index will help the query as all elements can come from the index instead of going back to the raw data pages per table.
SELECT
sched.sys_id
FROM
x_nuvo_eam_scheduled_m9e_e8s sched
LEFT JOIN x_nuvo_eam_scheduled_m10s maint
ON sched.scheduled_maintenance = maint.sys_id
WHERE
sched.status = 'Pending'
AND sched.scheduled_date >= '2022-02-15 06:00:00'
AND maint.asset IS NULL
ORDER BY
sched.sys_created_on ASC
limit
0, 100
CodePudding user response：
This index may help:
x_nuvo_eam_scheduled_maintena1: INDEX(asset, sys_id)
We can't see what `idx1 is; please help us.
Are there other differences in the Explains that you did not include?
Did you run each version twice -- to avoid caching differences?