Something to note up front, I am using Magento 2 so if the query looks a little strange, that's why.
I have the following query and am trying to optimize the indexes for the table;
SELECT `main_table`.`id`, `main_table`.`id`, `main_table`.`created_at` FROM `yotpo_order_status_history` AS `main_table` WHERE (`order_id` = 12709301) AND (`store_id` = 1) ORDER BY created_at DESC, id DESC LIMIT 1;
I'm not sure whether my alter query should include created_at and id only once, as such;
alter table yotpo_order_status_history add index (order_id, store_id, created_at, id);
Or if I should include them twice like so;
alter table yotpo_order_status_history add index (order_id, store_id, created_at, id, created_at, id);
I've looked for an answer to this question, but didn't find anything that even mentions this specific scenario. Should I go with the first option without defining the columns twice or do I need to use the second option? I'm familiar with redundant indexes and their performance implications but that's not quite what I've come across here.