Please look at the following Mysql table: http://pastebin.com/b0NDSbdz

I've partitioned the table by sent_at and I'm making sure that most of the queries pass sent_at. do I need to include sent_at in all of the indexes?

do you spot any potential redundant indexes?

when I do include the sent_at in the indexes, does it have to be the first field in the index declaration?


Multiple questions, multiple answers:

  • No need to "spot" potential redundant indexes: use either common_schema or Percona Toolkit to find such keys. But I've checked -- and no, there are no redundant keys.

  • You do not have to use sent_at in the indexes

  • If you do, it does not matter (partition-wise) where in the index.

But the one thing that is not told is how you query your table. Do you do an equality check on most fields and range-check on sent_at? In such case better have sent_at indexed last. If there is an equality check on sent_in or range check on same field, with little or no filtering on other columns, then have sent_in indexed by itself, or first in sequence. Right now you have 1 month of data in each partition; getting it indexed by time is not such a bad idea.

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.