Strange first partition behavior while pruning on table partitioned by date.

[Alexey Efremov]

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.