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
MySQL Dev Zone
MySQL Server Documentation
Oracle