MySQL Forums
Forum List  »  Partitioning

Partitions outside of date range is included
Posted by: Marius S
Date: March 18, 2010 10:22AM

Hi,

I have just tried to configure partitions based on date, but it seems that mysql still includes a partition with no relevant data. It will use the relevant
partition but also include the oldest for some reason. Am I doing it wrong?

The version is 5.1.44 (MyISAM)

I first made a few partitions based on "day", which is of type "date"

ALTER TABLE ptest
PARTITION BY RANGE(TO_DAYS(day))
(
PARTITION p1 VALUES LESS THAN (TO_DAYS('2009-08-01')),
PARTITION p2 VALUES LESS THAN (TO_DAYS('2009-11-01')),
PARTITION p3 VALUES LESS THAN (TO_DAYS('2010-02-01')),
PARTITION p4 VALUES LESS THAN (TO_DAYS('2010-05-01'))
);

After a query, I find that it uses the "old" partition, that should not contain any relevant data.

mysql> explain partitions select * from ptest where day between '2010-03-11' and '2010-03-12';
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | ptest | p1,p4 | range | day | day | 3 | NULL | 79 | Using where |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+-------------+


When I select a single day, it works:

mysql> explain partitions select * from ptest where day = '2010-03-11';
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+-------+
| 1 | SIMPLE | ptest | p4 | ref | day | day | 3 | const | 39 | |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+-------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Partitions outside of date range is included
4077
March 18, 2010 10:22AM


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.