At yesterdays MySQL Tech Tour in Dallas, a gentleman asked how to not only partition data but also store the indexes on other disks as a way of reducing I/O contention on drives.That struck a chord with me and I was soon looking up the information in the MySQL manual. I remembered there was a way to do this with MySQL 5.6 but I am sure the small screen of my cell phone and the detail level of the documentation did not convery the information as well as I had wished.
The example I tried to show was from the subpartition section of the MySQL manual. If you read 12.1.17 for the CREATE TABLE syntax, you will see how to add DATA DIRECTORY and INDEX DIRECTORY definitions to a partition. I guess the example I remembered got stuck in my brain cache (such as it is) because 5.6 allows subpartitioning. This allows you to store a column in a partition by a RANGE and the subpartition by a HASH or a KEY.
CREATE TABLE t1 (id INT, purchased DATE) PARTITION BY RANGE( YEAR(purchased) ) SUBPARTITION BY HASH( TO_DAYS(purchased) ) ( PARTITION p0 VALUES LESS THAN (2009) ( SUBPARTITION s0 DATA DIRECTORY = '/disk/a' INDEX DIRECTORY = '/disk/idx1', SUBPARTITION s1 DATA DIRECTORY = '/disk/b' INDEX DIRECTORY = '/disk/idx2' ), PARTITION p1 VALUES LESS THAN MAXVALUE ( SUBPARTITION s2 DATA DIRECTORY = '/disk/c' INDEX DIRECTORY = '/disk/idx3', SUBPARTITION s3 DATA DIRECTORY = '/disk/d' INDEX DIRECTORY = '/disk/idx4' ) );
A few others at the show asked about subpartitions and I promissed to blog about this.
And by the way, EXPLAIN PARTITION shows you how the optimizer wants to execute the query down to the subpartitions needed.
An example of EXPLAIN PARTITION on a subpartition
Make sure you create the directories for the DATA and INDEX entries and set the owner to mysql.
