Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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