Mysql partitioning: Partitions outside of date range is included
- by Sturlum
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 added 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 | |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+-------+
