MySQL Forums
Forum List  »  Partitioning

Re: Reasonable number of partitions?
Posted by: Jonathan Stephens
Date: March 09, 2007 12:39AM

Hi!

100 partitions * 100 subpartitions = 10,000 which isn't possible in MySQL 5.1, where the maximum number of user-defined partitions is 1024. This includes subpartitions.

Also, you can't subpartition a table that's partitioned by HASH. "In MySQL 5.1, it is possible to subpartition tables that are partitioned by RANGE or LIST. Subpartitions may use either HASH or KEY partitioning." (See http://dev.mysql.com/doc/refman/5.1/en/partitioning-subpartitions.html)

You should design your partitioning scheme keeping in mind the queries you'll run against the table. The idea is that you want to have to check as few of the table rows as possible, and you can do this very well in some cases by taking advantage of partition pruning. (See http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html)

If you have 100 partitions, and your results are typically coming from 90 of those, you're not really helping the situation very much - you're still having to check 90% of the rows. In some cases, you might actually do better to use only 10 partitions, but in such a way that results are coming from 3 partitions, then you're having to check only 30% of the rows, and you should see a considerable performance gain.

This could change somewhat in future with better parallelisation, but this won't happen in MySQL 5.1.

Jon Stephens
MySQL Documentation Team @ Oracle
Orlando, Florida, USA

MySQL Dev Zone
MySQL Server Documentation
Oracle

Options: ReplyQuote


Subject
Views
Written By
Posted
6693
March 08, 2007 10:09AM
Re: Reasonable number of partitions?
9970
March 09, 2007 12:39AM
4972
March 14, 2007 12:34PM
3647
September 20, 2007 08:36PM
3435
September 12, 2007 11:26AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.