MySQL - Create a Covering Index where the Sorting Option Columns are Identical to the Select Payload Columns

Question

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.

Answer 1

The first is the winner of the two. But it's not the best. The best one should be

alter table yotpo_order_status_history add index (order_id, store_id, created_at desc, id desc);

By default, the value is ordered asc, so for the first one, the database engine can't determine the right rowid without scanning all the id values.

Answer 2

Only once.

1. The = tests: order_id, store_id, in any order
2. The ORDER BY created_at, id, in the given order

(If you add non-= tests or GROUP BY or mix ASC and DESC, etc. the above rules are inadequate!)

(I cannot explain the duplication of main_table.id)

This tells you how to construct that index: Index Cookbook

(I don't remember ever hearing of repeating columns in the index. Based on how an indexes works, it should never be necessary.)