Mysql – Partition by year and sub-partition by month thesql

MySQLpartitioning

I have a database with a date column having datatype as date. I am using mysql 5.6

The table is expected to have 10k rows each day. I want to create partition on the table on year range and then sub partitions on months of that particular year.

I have tried is,

ALTER TABLE nav_master
    PARTITION BY RANGE( YEAR(nav_date) )
    SUBPARTITION BY HASH( MONTH(nav_date) )
    SUBPARTITIONS 12 (
        PARTITION january VALUES LESS THAN (2),
        PARTITION february VALUES LESS THAN (3),
        PARTITION march VALUES LESS THAN (4),
        PARTITION april VALUES LESS THAN (5),
        PARTITION may VALUES LESS THAN (6),
        PARTITION june VALUES LESS THAN (7),
        PARTITION july VALUES LESS THAN (8),
        PARTITION august VALUES LESS THAN (9),
        PARTITION september VALUES LESS THAN (10),
        PARTITION october VALUES LESS THAN (11),
        PARTITION november VALUES LESS THAN (12),
        PARTITION december VALUES LESS THAN (13)
    );

I am able to execute the above query on empty table. But when I insert or when I execute the above query in same table with some data I get the below error.

#1526 - Table has no partition for value 2014

Not able to understand what the exact error is.

Also let me know if sub partitions help in better optimizing the tables or I should just go with partitions?

Update: I was indeed able to create the partition. The issue now is that there is a NULL partition created. When I had ensured there is no null dates and also all the date conditions were handled while making partitions.

Question is how can I see what data has gone to NULL partition? So that I can get an idea on what the problem is.

I am getting a null partition name using this query.

SELECT PARTITION_NAME, TABLE_ROWS, PARTITION_EXPRESSION, PARTITION_DESCRIPTION FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'nav_master' limit 0,180

Using this query to get all rows in partition, question is what to pass as partition name for null?

SELECT count(*) FROM nav_master PARTITION(??);

Best Answer

I had to do the same thing and solved slightly differently. Basically as far as I understand by reading the docs MySQL subpartitioning does not support partition types besides HASH and KEY.

In MySQL 5.6, it is possible to subpartition tables that are partitioned by RANGE or LIST. Subpartitions may use either HASH or KEY partitioning. This is also known as composite partitioning.

This means that we can't determine in what subpartition a record will end up. It's up to MySQL. So I don't think it's wise giving such names to your subpartitions (i.e. january) because no way you're going to know if stuff created on that month will end up there. This because you can't subpartition by MONTH(nav_date) but only by HASH(MONTH(nav_date)) or KEY(MONTH(nav_date)).

So to solve the problem I decided to create a new month column in my table and then I added an index to it. Then I subpartioned by KEY(month) without caring about the subpartition names. This way in MySQL 5.6 I could select the main partition from the FROM clause and the subpartition by specifying the month in the WHERE clause.

Follows the full example:

CREATE TABLE `my_example` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `value` double DEFAULT NULL,
    `is_deleted` tinyint(1) NOT NULL DEFAULT '0',
    `timestamp` datetime NOT NULL,
    `last_modified` datetime NOT NULL,
    `month` tinyint(1) NOT NULL,
    PRIMARY KEY (`id`,`timestamp`, `month`),
    KEY `in_is_deleted` (`is_deleted`),
    KEY `in_last_modified` (`last_modified`),
    KEY `in_timestamp` (`timestamp`),
    KEY `in_month` (`month`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
PARTITION BY RANGE (YEAR(`timestamp`))
SUBPARTITION BY KEY (`month`) 
SUBPARTITIONS 12 (
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION p2014 VALUES LESS THAN (2015),
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015); -- will go through all twelve subpartitions
EXPLAIN PARTITIONS SELECT * FROM my_example PARTITION (p2015) WHERE month = 9; -- will look only into one subpartition
Related Question