MYSQL Query taking too much time for false scenarios

Question

I have a query like this,

select o.order_id 
FROM orders o 
WHERE o.order_name = 'XL' 
AND o.order_status='SUCCESS' 
AND o.order_type='LARGE' 
ORDER by o.created_at ASC limit 1;

This is working fine when it has a returns a value. But it takes very long to execute for scenarios where the query has no records to return.

I have added separate index on created_at(idx_created_at) and (order_name, order_status, order_type)(idx_name_status_type) but with no improvements.

This is the query plan

+----+-------------+-------+------------+------+-----------------------------------------------------------+---------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys                                             | key                 | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+-----------------------------------------------------------+---------------------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | o     | NULL       | ref  | orders_order_status,idx_name_status_type                  | orders_order_status | 1       | const |    1 |     5.00 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------------+------+-----------------------------------------------------------+---------------------+---------+-------+------+----------+----------------------------------------------------+

Could you please let me know what might be causing this issue.

Answer 1

Firstly you are using your index from your execution plan but there is a cost of sorting.

I would add created_at column in the idx_name_status_type index, because you are doing ORDER by o.created_at ASC limit 1 which will try to order the result set after the query filter the condition.

CREATE INDEX idx_name_status_type 
ON orders (order_name, order_status, order_type,created_at);