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.

I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I understand, most queries on a column used in the partition will be faster because the query will (for most queries) only have to search within the partition applicable to the query. Thus, it would make sense that, in order to maximize efficiency, you should divide a large table into the maximum number of partitions, therefore, making each partition as small as possible. In the case of MySQL, this means 1024 partitions. But is there any performance drawback to having a large number of partitions? Is so, how does one find the optimum number of partitions?

Note: There is a somewhat similar question already, but only one answer, which (from my perspective) misses the mark. So I will state the question in my own way...hopefully it is more clear

share|improve this question

migrated from stackoverflow.com Sep 21 '12 at 22:40

This question came from our site for professional and enthusiast programmers.

    
Ok. Should I just repost there, and delete this question? –  robguinness Sep 13 '12 at 14:47
    
Voted to move to DBA. –  Kermit Sep 13 '12 at 14:51

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.