How to optimize SQL with WHERE and ORDER BY I have the SQL statement:

SELECT erp_orders_id
FROM erp_orders o
WHERE o.orders_export_time &gt;= '2015-09-20'
ORDER BY o.erp_orders_id ASC
LIMIT 1


where erp_orders_id is primary key of type int. and orders_export_time is timestamp.
I'm trying to optimize it by creating an index on both orders_export_time and erp_orders_id:

ALTER TABLE `erp_orders` 
ADD KEY `IDX_ORDERID_EXPORTTIME`(`erp_orders_id`,`orders_export_time`) USING BTREE;


However, EXPLAIN shows it chooses PRIMARY to filter data:

+--------------+-----------------------+-----------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+-----------------+
| id           | select_type           | table           | type           | possible_keys           | key           | key_len           | ref           | rows           | Extra           |
+--------------+-----------------------+-----------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+-----------------+
| 1            | SIMPLE                | o               | index          | orders_export_time      | PRIMARY       | 4                 |               | 5              | Using where     |
+--------------+-----------------------+-----------------+----------------+-------------------------+---------------+-------------------+---------------+----------------+-----------------+


QUESTIONS

1 : I don't understand why primary is chosen here?

So I tried to force using index IDX_ORDERID_EXPORTTIME and get the following plan which seems good to me:

+--------------+-----------------------+-----------------+----------------+-------------------------+------------------------+-------------------+---------------+----------------+--------------------------+
| id           | select_type           | table           | type           | possible_keys           | key                    | key_len           | ref           | rows           | Extra                    |
+--------------+-----------------------+-----------------+----------------+-------------------------+------------------------+-------------------+---------------+----------------+--------------------------+
| 1            | SIMPLE                | o               | index          |                         | IDX_ORDERID_EXPORTTIME | 8                 |               | 1              | Using where; Using index |
+--------------+-----------------------+-----------------+----------------+-------------------------+------------------------+-------------------+---------------+----------------+--------------------------+


With the plan above, I assume it would be much faster than the first query. However, when I execute it, it runs pretty slow, even slower than the first one. So

2 : Why is this query so slow even with execution plan indicates that it scanned only 1 row?

3 : How am I supposed to optimize this query?
Q1: I don't understand why primary is chosen here?


The index you made helps neither the WHERE or the ORDER BY due to the columns in the wrong order.

Since you selected only the erp_orders_id, the PRIMARY KEY was chosen.


  Q2: Why is this query so slow even with execution plan indicates that it scanned only 1 row?


Since you forced the index, the entire index was scanned until one row was found that matched o.orders_export_time &gt;= '2015-09-20'. If you have rows from earlier months, even earlier years, those rows would be read until '2015-09-20' was finally found.


  Q3 how am I supposed to optimize this query?


As @dnoeth said in the comment, reverse the column order in the index.

ALTER TABLE `erp_orders` 
ADD KEY `IDX_ORDERID_EXPORTTIME`(`orders_export_time`,`erp_orders_id`) USING BTREE;