I have the SQL statement:
SELECT erp_orders_id
FROM erp_orders o
WHERE o.orders_export_time >= '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?
Best Answer
No single index (composite or not) will be optimal
You can provide both of these, and let the Optimizer pick:
On the other hand, this would be a "covering" index for that specific query:
and might be better than putting them in the opposite order.