MySQL Forums
Forum List  »  Partitioning

Re: Pruning by month
Posted by: Michael Taggart
Date: October 23, 2008 07:21PM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
10071
October 23, 2008 04:56AM
4337
October 23, 2008 06:12PM
4563
October 23, 2008 06:17PM
4273
October 23, 2008 06:46PM
Re: Pruning by month
4191
October 23, 2008 07:21PM
4003
October 25, 2008 06:42PM
3859
February 12, 2009 08:42AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.