User-defined partitioning looks like it is set to be a useful feature in MySQL. It allows you to distribute portions of individual tables across a file system according to rules which you can set largely as needed.
I was looking into it to overcome the obstacles of running OPTIMIZE against large tables, because when optimizing you need more free disk space than the size of the table to optimize it, otherwise MySQL will just sit there idling, not completing the operation but not failing with an error. It just sits there, locking the table and idles (this is a reported bug).
By partitioning, I could split a large table into several pieces, and then optimize each of these separately and not need as much free space on the disk.
Partitioning can also make some queries run faster because MySQL can tell which partition the data is sitting on and only search that one rather that having to search the entire set of data.
However – tucked away at the bottom of one of the last manual pages is a very important limitation: Key caches are not supported for partitioned tables.
The key cache helps to minimize disk I/O by putting table indexes into memory, so by using a partitioned table, each time a query is executed the index files are either accessed from the native file system buffering (provided by the operating system) or it has to be read from disk. I’m not sure I want to try that on a table with several million rows.
Key caching for partitioned tables was fixed by bug #39637 but only available in MySQL 5.5, which isn’t yet suitable for production.
For now, I’m going to create a merge table based on a collection of small tables and optimize these separately when necessary.