I have a query running against Maridb, when we query in ASC order, the optimizer checks less number of records (r_rows) and completes the query in about 500 ms, but when switching the order to DESC, then The same query takes more time to complete with approximately 2.27 million r_rows.
Why is this? Why does ASC/DESC order affect query performance?
This is a 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
The following 2 MariaDB analysis outputs show the execution plan
ASC ordered query completed in about 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 completed ~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'" |
| | },
+---------+-----------------------------------------------------------------------------------------------------------------------
P粉8484421852024-02-04 18:24:16
Index optimization suggestions
Table index x_nuvo_eam_scheduled_m9e_e8s (status, scheduled_date, scheduled_maintenance, sys_created_on) x_nuvo_eam_scheduled_m10s (sys_id)
Then, modified to have no (parens) and ticks, but also use cleaner aliases maintained by scheduled vs. It will help to have the first table with appropriate indexes to optimize the WHERE and JOIN criteria. But creating a complete covering index will also help with queries because all elements can come from the index instead of going back to the original data page for each 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