I love the way partitioning works in MySQL. I remember in the past how many projects I implemented using application logic to parallelize I/O. Partitioning makes this seamless now. But it's not without its share of problems and workarounds. So I compiled my own wishlist that hopefully might make it into a future version of MySQL.
1. Partition level table locking. Partitions should be treated like tables and locked individually rather than the who table and all of its partitions.
2. Ability to add partitions from existing tables. This is very ueful, especially when trying to perform bulk maintainance operations.
3. Ability to convert a partition to a table.
4. Be able to mix and match storage engines for partitions and subpartitions. How cool would it be to have an archive partition for older data reside using ARCHIVE tables while the remaining partitions are InnoDB or MyISAM.
5. More usuable datatypes for partition pruning. How many times can you use datetme when timestamp is available. Also when would functions other than TO_DAYS and YEAR be supported?
2 comments:
Without delivering any promises I can reply that your wishlist is more or less an exact copy of mine, there are also a number of additional items on mine as well. Particularly to take some first steps towards parallelisation.
Which release they end up in as usual uncertain.
truncate partition.
All the date-related features for cyclic table exist (you can partition by day % 7, or by seconds %300), but as long as we can't truncate a single transaction we can't have a sliding-window for historic data...
Also, Insert-Select should not lock all partitions, only those that comply with the select condition
Post a Comment