One good feature in MySQL 5.1 is the support of partitions which is built into the MySQL server. Lot of applications that I noticed use their own partition schemes by splitting the large table data to multiple tables on a desired key and wrapper table or application logic that identifies what table to use.
Here comes MySQL to rescue all those applications to push the partition logic to MySQL server itself without needing a application specific logic; which maintains partitions underneath the covers (each partition is a sub-table) on a single large table as shown below:
The partition key is specific to table and/or application. If the partition key is account_id or site_id or anything that takes both active writes and reads; it is easy to make the partition table as InnoDB; so that they can really scale well.
If the partitions purpose is to serve reads; then making it MyISAM makes much better sense. Again, MyISAM with partitions does not support KEY CACHE or LOAD INDEX on partitioned table (hopefully in the future versions we can expect), that leaves to use only global key_buffer_size.
By the way, all the tests that I did so far, proves that MyISAM works well with partitions when compared to InnoDB due to stats that InnoDB provides to optimizer on partitions (not always) seems to be wrong especially when you have a partition on a range which is not unique (unless the application logic is forced to use INDEX or have the right join order and use STRAIGHT_JOIN).
The real problem comes when you have a table where active writes happens on a particular range (like current month) and rest of them are used as logging or serving for reports as shown below:
DROP TABLE IF EXISTS test_part; CREATE TABLE test_part( date DATE NOT NULL DEFAULT '0000-00-00', comment VARCHAR(20) DEFAULT NULL )ENGINE=MyISAM PARTITION BY RANGE (to_days(date)) ( PARTITION nov08 VALUES LESS THAN(TO_DAYS('2008-12-01')), PARTITION dec08 VALUES LESS THAN(TO_DAYS('2009-01-01')), PARTITION jan09 VALUES LESS THAN(TO_DAYS('2009-02-01')), PARTITION feb09 VALUES LESS THAN(TO_DAYS('2009-03-01')), PARTITION mar09 VALUES LESS THAN(TO_DAYS('2009-04-01')), PARTITION unpart VALUES LESS THAN MAXVALUE );
In this case, choosing a right table type is more complex as making it MyISAM blocks the reads on active writes to current month; and choosing InnoDB makes it use more tablespace and memory along with performance penalty.
The one solution for this is to make active writes partition (current month, partition mar09 in the above example) as InnoDB and rest of the partitions as MyISAM. Unfortunately MySQL does not yet support mixing of table types within the partitions.
mysql> mysql> CREATE TABLE test_part( -> date DATE NOT NULL DEFAULT '0000-00-00', -> comment VARCHAR(20) DEFAULT NULL -> )ENGINE=MyISAM -> PARTITION BY RANGE (to_days(date)) -> ( -> PARTITION nov08 VALUES LESS THAN(TO_DAYS('2008-12-01')), -> PARTITION dec08 VALUES LESS THAN(TO_DAYS('2009-01-01')), -> PARTITION jan09 VALUES LESS THAN(TO_DAYS('2009-02-01')), -> PARTITION feb09 VALUES LESS THAN(TO_DAYS('2009-03-01')), -> PARTITION mar09 VALUES LESS THAN(TO_DAYS('2009-04-01')) ENGINE=InnoDB, -> PARTITION unpart VALUES LESS THAN MAXVALUE -> ); ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MySQL mysql>
So, usage of partitions on date ranges with writes happening in the current month, will have a tough decision to make until MySQL supports mix of table handlers within the partitioned table.