1

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.

2
  • 1
    The first. EXPLAIN output should confirm Commented May 5, 2023 at 18:23
  • I thought so, but I wasn't sure. Thanks.
    – Marisa
    Commented May 5, 2023 at 18:28

2 Answers 2

0

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.

3
  • note, full recognition and implementation of desc indexes comes with 10.8+. Note the primary key (assumed to be id) is already at the end of every secondary index (always ascending). So perhaps a created_at (on default asc) is all that's needed at the end. With both of them ascending, a reverse search of an index is possible.
    – danblack
    Commented May 8, 2023 at 8:36
  • 1
    agreed, if id is the primary key, then an index on (order_id, store_id, created_at) will be the best option. Commented May 8, 2023 at 9:49
  • look at explain key_len to determine how much of an index is used.
    – danblack
    Commented May 9, 2023 at 0:31
1

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.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.