I tried loading the FlightStats database (feel free to get your copy on dl.flightstats.us) into a MySQL 5.1 instance to try out partitioning. It seemed fairly simple… create an ontime table, partitioned by month, er, wait. Then I hit this bug:
mysql> CREATE TABLE t ( -> d DATE NOT NULL -> ) -> PARTITION BY RANGE(d) ( -> PARTITION p0 VALUES LESS THAN ('2005-06-02') -> ); ERROR 1064 (42000): VALUES value must be of same type as partition function near ') )' at line 5
OK, so I worked around that using YEAR(d)*100+MONTH(d) instead, and calling it an INT. I got a few million rows of data loaded. I tried one of the main features that partitioning is useful for, and one that MySQL claims to support: dropping single partitions. Immediately, I hit this bug:
mysql> alter table ontime drop partition p_2000_01; ERROR 1037 (HY001): Out of memory; restart server and try again (needed 8 bytes)
Ugh. I guess I have to give up and wait for MySQL 5.1.8 before I can try again. Keep an eye out, I’ll update things once I try again.