3

If I have a table with the following primary key:

PRIMARY KEY (id)

And I want to query documents written by someone (e.g. display a preview of the latest 20), sorted by their "id", so I created an index:

INDEX from_id (from, id)

Is the "id" at the end of this index redundant because it ends with the primary key?
If yes, why?

0

In InnoDB, every "secondary" index (that is, index other than "PRIMARY") automatically adds the columns of the PK.

These are 'identical':

PRIMARY KEY(id),  INDEX(from, id)
PRIMARY KEY(id),  INDEX(from)

You could even do

PRIMARY KEY(from, id),  INDEX(id)

This combo is beneficial when id is AUTO_INCREMENT, but you often do a range on from. Now the rows are "clustered" together, thereby being more efficient to fetch. Meanwhile, INDEX(id) keeps AUTO_INCREMENT happy -- since it needs to know where it left off.

I assume you are doing:

SELECT ...
    WHERE `from` = '...'
    ORDER BY id DESC
    LIMIT 20;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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