Mysql Partition : how to deal with month and hash

Hi all,

I have a specific question on mysql sub-partitioning using hash on a date/datetime column. I have partitioned by site_id, and I want now to subpartitioned by month (1 to 12), so the partitions number is fixed in time (sp_dec, sp_jan, ...)

Current cut (columns and other partitions) table structure :

CREATE TABLE IF NOT EXISTS `email` (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `sent_date` datetime NOT NULL,
  `site_id` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`,`site_id`,`sent_date`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1
PARTITION BY LIST (site_id)
SUBPARTITION BY HASH (MONTH(sent_date)) (
PARTITION p0 VALUES IN (1,3,4) (
  SUBPARTITION dec0 ENGINE = InnoDB,
  SUBPARTITION jan0 ENGINE = InnoDB,
  SUBPARTITION feb0 ENGINE = InnoDB,
  SUBPARTITION mar0 ENGINE = InnoDB,
  SUBPARTITION apr0 ENGINE = InnoDB,
  SUBPARTITION may0 ENGINE = InnoDB,
  SUBPARTITION jun0 ENGINE = InnoDB,
  SUBPARTITION jul0 ENGINE = InnoDB,
  SUBPARTITION aug0 ENGINE = InnoDB,
  SUBPARTITION sep0 ENGINE = InnoDB,
  SUBPARTITION oct0 ENGINE = InnoDB,
  SUBPARTITION nov0 ENGINE = InnoDB)
);

I have seen that I should not use MONTH function for pruning. Most examples use Range to manage month problems, but it's not available in subpartition...

Firstly, I failed to use datetime, since my_date='2010-09-27' failed because of time part so I need to use between or >= tips. By doing so, pruning is no more applied so all partitions are used !

Secondly, I splitted datetime into two columns and now, my_date='2010-09-27' works fine. However, interval problem is still here and do not use my partitions.

EXPLAIN PARTITIONS SELECT * FROM `email` WHERE sent_date >='2010-09-27'
AND sent_date<'2010-09-28';

EXPLAIN PARTITIONS SELECT * FROM `email` WHERE
sent_date BETWEEN '2010-09-20' AND '2010-09-27';

Note : I just have test datas, so I can modify my date field or partitions easily.

How to partition by month using hash or key, working with intervals too ?

Thanks for reading :)

ansaurus

tags:

views:

answers:

Mysql Partition : how to deal with month and hash

related questions