Why is DESC command query slow on MariaDB

Question

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

Answer 1

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