MySQL Forums
Forum List  »  Partitioning

Strange first partition behavior while pruning on table partitioned by date.
Posted by: Alexey Efremov
Date: June 29, 2010 09:46AM

Maybe someone can help me with this issue.

I have a simple table like:

CREATE TABLE `j` (
`user` int(11) NOT NULL,
`at` DATETIME NOT NULL default '2012-04-23',
KEY `user` (`user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE(TO_DAYS(at)) (
PARTITION p0501 VALUES LESS THAN (TO_DAYS('2010-05-01')),
PARTITION p0601 VALUES LESS THAN (TO_DAYS('2010-06-01')),
PARTITION p0611 VALUES LESS THAN (TO_DAYS('2010-06-11')),
PARTITION p0622 VALUES LESS THAN (TO_DAYS('2010-06-21')),
PARTITION p0701 VALUES LESS THAN (TO_DAYS('2010-07-01')),
PARTITION p0711 VALUES LESS THAN (TO_DAYS('2010-07-11')),
PARTITION p0722 VALUES LESS THAN (TO_DAYS('2010-07-21')),
PARTITION p9999 VALUES LESS THAN MAXVALUE
);

Just a simple weekly-partitioned table for some kind of logging.
When I select exact value from this table, it prunes ok:

mysql> explain partitions select * from j where at = now() ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | j | p0701 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+

But when I try to select something GREATER than current date, I receive following:

mysql> explain partitions select * from j where at >= now() ;
+----+-------------+-------+-------------------------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------------------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | j | p0501,p0701,p0711,p0722,p9999 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
+----+-------------+-------+-------------------------------+------+---------------+------+---------+------+------+-------------+

Why p0501 is there? Is it looping of some kind, or what?

Then, when I try to check more realistic query, It says:

mysql> explain partitions select * from j where at BETWEEN DATE_SUB(NOW(), interval 5 day ) and NOW();
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | j | p0501,p0701 | ALL | NULL | NULL | NULL | NULL | 8 | Using where |
+----+-------------+-------+-------------+------+---------------+------+---------+------+------+-------------+

Why p501 is so attractive to MySQL? Where is my mistake or misunderstanding?

Thanks for any help in advance!



Edited 1 time(s). Last edit at 06/29/2010 09:50AM by Alexey Efremov.

Options: ReplyQuote


Subject
Views
Written By
Posted
Strange first partition behavior while pruning on table partitioned by date.
3508
June 29, 2010 09:46AM


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.