Re: Use of merge tables
We have spent most of this year (2008) trying to implement a partitioned table for an application that analyzes ~100 million rows of data each month. The table has over 1.4 Billion records total. I could write pages on the various issues we've encountered, but in the end, we've concluded that a MySQL partitioned table just isn't suited for this application.
Although our application queries data going back a couple years, most of the "real" activity (INSERTs, UPDATEs, and most SELECTs) is on the very tail end of the dataset. To get acceptable performance from the partitioned table, we had to modify almost all of our queries to include date filters to trigger partition pruning, but this almost always prevented subsequent JOINS on other fields from being indexed. This really dragged down performance.
Then MySQL removed the "REPAIR PARTITION" option from the ALTER TABLE command in 5.1.x, meaning that our entire database is now at risk from a single instance of database corruption. This point was driven home earlier this week when a few entries in the index file on a single monthly partition got corrupted (alarming in itself) and our only solution was to shut the database down and run the myisamchk utility against that individual partition's index file. Using that utility on a single partition is now officially unsupported, so we feared for half a day that the entire database was lost. I guess we could have used CHECK TABLE, but with a 200GB database, the resulting table rewrite would quite literally take weeks.
Given all of these problems, we've decided to try MERGE tables instead. They're admittedly a step back from genuine partitioning, but being able to access the monthly subtables ("partitions") individually is a huge plus. We can REPAIR them individually, run "myisampack" on them individually, and aggregate them in whatever combinations make sense for what we're doing. (It's no mean feat to add a FULL partition back to a partitioned table, BTW, so once you drop a partition of data off the back end, it's gone.)
So anyway, I strongly urge MySQL to keep MERGE tables in the mix -- at least until partitioned tables have a few more years to mature.