Efficient Method To Partition MySQL Table By Year and Month
mysql, partitioning
Solution
- `SUBPARTITIONs` are probably useless. (That is, I have yet to find any advantage to their use. That especially applies to performance.)
- Don't split the date; keep it as a single field.
- Use `BY RANGE(TO_DAYS(dt)) VALUES LESS THAN (TO_DAYS('2015-02-01'))`
- `BY HASH` is probably totally useless for performance.
- `WHERE dt BETWEEN .. AND ..` cannot do partition pruning in the structure you have.
- Do not use more than about 50 partitions (for performance reasons).
- Do not create more than one 'future' partition; build them as needed. (This is a minor performance improvement.)
- Do not use `CHAR` for variable length fields. Use `VARCHAR`.
Problem
I am exploring ways of partitioning a MySQL table by year and month. Can you please analyze my table creation below and see if this method of partitioning would end up putting data by month and year in these sub partitions? I'm using MySQL 5.5 and I can't use ``` SELECT * FROM points_log PARTITION (p0_p0sp0); ``` to validate if the partitioning is working. If there is a way to validate this in MySQL 5.5 please comment. I appreciate your feedback and criticisms on this table partitioning. Here is my table creation: ``` CREATE TABLE `points_log` ( `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT, `nick` char(25) NOT NULL, `amount` decimal(7,4) NOT NULL, `stream_online` tinyint(1) NOT NULL, `modification_type` tinyint(3) unsigned NOT NULL, `dt` datetime NOT NULL, PRIMARY KEY (`id`,`dt`,`nick`), KEY `nick_idx` (`nick`), KEY `amount_idx` (`amount`), KEY `modification_type_idx` (`modification_type`), KEY `dt_idx` (`dt`), KEY `stream_online_idx` (`stream_online`) ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1 PARTITION BY RANGE( YEAR(dt) ) SUBPARTITION BY HASH( MONTH(dt) ) SUBPARTITIONS 12 ( PARTITION p0 VALUES LESS THAN (2014), PARTITION p1 VALUES LESS THAN (2015), PARTITION p2 VALUES LESS THAN (2016), PARTITION p3 VALUES LESS THAN (2017), PARTITION p4 VALUES LESS THAN (2018), PARTITION p5 VALUES LESS THAN (2019), PARTITION p6 VALUES LESS THAN (2020), PARTITION p7 VALUES LESS THAN MAXVALUE ); ```