MySQL’s new partitioning feature is a way to split tables up into chunks based on various criteria. For instance, you could break up a table based on a timestamp and rows older than a specific date are stored in MySQL’s archive storage engine.

1. Partitioning will first appear in version 5.1.
2. Partitioning is a “Divide and Conquer” solution for large tables.
3. You can partition data based on year. For instance you can put data from before 1999 into /var/data/part1, before 2001 onto /var/data/part2, etc.
4. You can move data from partition to partition as data becomes stale as well.
5. Sits above the storage engine layer and works with all table types. Sits in its own layer.
6. You can remove partitioning with ALTER TABLE t1 REMOVE PARTITIONING.
7. Limits on partitioning include that the partition function must return an integer result, all partitions must use the same storage engine, and it increases response time with many partitions.
8. You can partition by odd and even values. For instance, you can put even values into partitions 2, 4, 6 and 8 and odd values into partitions 1, 3, 5 and 7.
9. You can partition by hashing, which lets MySQL decide where to put the data. There isn’t a logical distribution between partitions so it makes pruning more difficult. For instance, you never really know which partition the data is on (unlike the above example).
10. You can create composit partitions as well, which are essentially sub-partitions.
11. If a primary key is defined no fields outside of primary key is allowed in partition function (wtf?).
12. You can reorganize partitions whenever you want. For instance you can roll partition 2 into partition 3 and move their locations. This, however, locks the table until the partition operations have completed.
13. If you have both a PRIMARY KEY and a UNIQUE index in a single you cannot do partitioning on that table.
14. If a storage engine has a table size limit of 64GB (ie. InnoDB) you can have 64GB on each partition. This essentially allows you to scale beyond your storage engine’s table size limit.
15. Did I just hear something about foreign key constraints being put into the meta storage engine in the near future? This would allow foreign key constraints for all table types I believe.
16. Creating, updating, altering, etc. of partitions is done atomically. Either the change to the partition happens or it’s rolled back and an error message is returned.
17. If one of your partitions goes away all of your data goes away. Yikes!
18. It doesn’t sound like you can partition federated tables.
19. Paritioning does work with replication.
20. You can use it with auto-increment primary keys.

This is a very cool feature for people who have very large data sets and are looking to break those data sets up into smaller data sets. For instance, if you have a table with 1m records in it you could split it up into two data sets of 500k records in each data set, which would reduce the number of rows MySQL needs to scan during queries.