Efficient Method To Partition MySQL Table By Year and Month

Question

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
   );

Answer 1

• 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.