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'" | | | }, +---------+-----------------------------------------------------------------------------------------------------------------------
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.