Mysql partitioning: Partitions outside of date range is included
Posted
by Sturlum
on Stack Overflow
See other posts from Stack Overflow
or by Sturlum
Published on 2010-03-19T10:01:52Z
Indexed on
2010/03/19
10:11 UTC
Read the original article
Hit count: 246
mysql
|database-partitioning
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 | |
+----+-------------+------------+------------+------+---------------+------+---------+-------+------+-------+
© Stack Overflow or respective owner
