title: Efficient Method To Partition MySQL Table By Year and Month
body: <p>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</p>
  
  <pre><code>SELECT * FROM points_log PARTITION (p0_p0sp0);
  </code></pre>
  
  <p>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. </p>
  
  <p>Here is my table creation:</p>
  
  <pre><code>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
     );
  </code></pre>