MySQL 5.6 Partitons and Subpartititions


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.

EXPLAIN PARTITION

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.

About these ads

Leave a comment

Filed under MySQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s