How do you order the indexing columns in MySQL if you are using order by in your query?

user_3759007 0
Viewed 519

I am reading an article about how Pinterest shards their MySQL database: https://medium.com/@Pinterest_Engineering/sharding-pinterest-how-we-scaled-our-mysql-fleet-3f341e96ca6f

And here they have an example of a table:

CREATE TABLE board_has_pins (
  board_id INT,
  pin_id INT,
  sequence INT,
  INDEX(board_id, pin_id, sequence)
) ENGINE=InnoDB;

And they are showing how they query from that table:

SELECT pin_id FROM board_has_pins 
WHERE board_id=241294561224164665 ORDER BY sequence 
LIMIT 50 OFFSET 150

What I don't understand here is the ordering of the index. Would it not make more sense if the index was like this since they are ordering by sequence and filtering by board_id?

INDEX(board_id, sequence, pin_id)

Am I missing something here or have I misunderstood how indexing works?

indexing mysql
1 Answers

You are correct. The better index for this query is:

INDEX(board_id, sequence, pin_id)

The columns should be in this order:

  • Column(s) involved in equality comparisons. If there are multiple columns, their order does not matter.
  • Column(s) involved the ORDER BY clause, in the same order they appear in the ORDER BY.
  • Other columns used to fetch values, like pin_id.

Once the equality conditions find the subset of matching rows, they are all tied with respect to their order, because naturally they all have the same value for the column of the quality condition (board_id in this case).

The tie is resolved by the order of the next column in the index. If (and only if) the next column is the one used in the ORDER BY clause, then the rows can be read in index order, with no further work needed to sort them.

I don't know what is the explanation for the Pinterest blog post you linked to. I guess it's a mistake, because the index is not optimal for the query they showed.

Bill_Karwin_208600
Related