Re: Pruning by month
Sorry, I had some typos in my last post. This is the right syntax to create the table and the trigger:
CREATE TABLE `test3` (
`id` int(11) NOT NULL,
`dtime` datetime NOT NULL,
`dmonth` smallint(2) NOT NULL,
`value` bigint(20) NOT NULL,
KEY `test3_id_dtime_idx` (`id`,`dtime`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1
PARTITION BY LIST (dmonth)
(PARTITION pJan VALUES IN (1),
PARTITION pFeb VALUES IN (2),
PARTITION pMar VALUES IN (3),
PARTITION pApr VALUES IN (4),
PARTITION pMay VALUES IN (5),
PARTITION pJun VALUES IN (6),
PARTITION pJul VALUES IN (7),
PARTITION pAug VALUES IN (8),
PARTITION pSep VALUES IN (9),
PARTITION pOct VALUES IN (10),
PARTITION pNov VALUES IN (11),
PARTITION pDec VALUES IN (12));
delimiter |
CREATE TRIGGER month_insert BEFORE INSERT ON `test3`
FOR EACH ROW
BEGIN
SET NEW.dmonth = MONTH(NEW.dtime);
END;
|
Only problem with this setup is this:
mysql> EXPLAIN PARTITIONS SELECT * FROM test.test3 WHERE dtime >= '2008-04-01' AND dtime <= '2008-04-30';
+----+-------------+-------+-------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test3 | pJan,pFeb,pMar,pApr,pMay,pJun,pJul,pAug,pSep,pOct,pNov,pDec | ALL | NULL | NULL | NULL | NULL | 7 | Using where |
+----+-------------+-------+-------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
However, if you add "AND dmonth=4" to the query you get the desired results. Therefore, there are still some application changes needed.
mysql> EXPLAIN PARTITIONS SELECT * FROM test.test3 WHERE dtime >= '2008-04-01' AND dtime <= '2008-04-30' AND dmonth=4;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test3 | pApr | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)