| Bug #60440 | RANGE partitioning on TIMESTAMP column does not prune partitions | ||
|---|---|---|---|
| Submitted: | 12 Mar 2011 8:24 | Modified: | 29 Apr 2011 9:56 |
| Reporter: | Priyank Patil | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S2 (Serious) |
| Version: | 5.5.9 | OS: | Any (Windows XP - 32 bit) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Partition pruning, Partitiong | ||
[12 Mar 2011 8:24]
Priyank Patil
[12 Mar 2011 8:45]
Valeriy Kravchuk
I think http://dev.mysql.com/doc/refman/5.5/en/partitioning-pruning.html explains this. Partition pruning for BETWEEN happens only if it can be rewritten as IN (...) with a small list of possible values (less than number of partitions): "Pruning can also be employed for short ranges, because the optimizer can turn such conditions into IN relations. For example, using the same table t4 as defined previously, queries such as these can be pruned: SELECT * FROM t4 WHERE region_code > 2 AND region_code < 6; SELECT * FROM t4 WHERE region_code BETWEEN 3 AND 5; In both these cases, the WHERE clause is transformed by the optimizer into WHERE region_code IN (3, 4, 5). Important This optimization is used only if the range size is smaller than the number of partitions. Consider this query: SELECT * FROM t4 WHERE region_code BETWEEN 4 AND 12; The range in the WHERE clause covers 9 values (4, 5, 6, 7, 8, 9, 10, 11, 12), but t4 has only 8 partitions. This means that the previous query cannot be pruned." In your case there are 10 partitions, but compare that to number of seconds in one day (you partition by TIMESTAMP column)! So, this is a documented limitation, not a bug (IMHO).
[12 Mar 2011 9:14]
Priyank Patil
Thanks for your quick reply Valeriy. For any practical purpose, number of seconds is going to be a lot more than the number of partitions. So, that means partitioning on TIMESTAMP, cannot be used to prune data. One can perhaps get other partitioning benefits such as dropping of partitions, but for practical purposes, it would not help in pruning. Someone please confirm this. Is there any plan to change this? If not, I think it would be a good idea to add a small note in the RANGE Partitioning documentation.
[12 Mar 2011 10:22]
Valeriy Kravchuk
This is easy to verify:
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -proot -P3310 test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 51
Server version: 5.1.54-community MySQL Community Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> CREATE TABLE quarterly_report_status (
-> report_id INT NOT NULL,
-> report_status VARCHAR(20) NOT NULL,
-> report_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
-> CURRENT_TIMESTAMP
-> )
-> PARTITION BY RANGE ( UNIX_TIMESTAMP(report_updated) ) (
-> PARTITION p0 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-01-01 00:00:00')
),
-> PARTITION p1 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-04-01 00:00:00')
),
-> PARTITION p2 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-07-01 00:00:00')
),
-> PARTITION p3 VALUES LESS THAN ( UNIX_TIMESTAMP('2008-10-01 00:00:00')
),
-> PARTITION p4 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-01-01 00:00:00')
),
-> PARTITION p5 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-04-01 00:00:00')
),
-> PARTITION p6 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-07-01 00:00:00')
),
-> PARTITION p7 VALUES LESS THAN ( UNIX_TIMESTAMP('2009-10-01 00:00:00')
),
-> PARTITION p8 VALUES LESS THAN ( UNIX_TIMESTAMP('2010-01-01 00:00:00')
),
-> PARTITION p9 VALUES LESS THAN (MAXVALUE)
-> );
Query OK, 0 rows affected (0.97 sec)
mysql> insert into quarterly_report_status values (1,'OK','2008-07-01 01:00:00'
);
Query OK, 1 row affected (0.09 sec)
mysql> explain partitions select * from quarterly_report_status where report_upd
ated
-> between '2008-07-01 00:00:40' and '2008-08-01 00:00:40';
+----+-------------+-------------------------+-------------------------------+--
----+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | t
ype | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+-------------------------------+--
----+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | quarterly_report_status | p0,p1,p2,p3,p4,p5,p6,p7,p8,p9 | A
LL | NULL | NULL | NULL | NULL | 10 | Using where |
+----+-------------+-------------------------+-------------------------------+--
----+---------------+------+---------+------+------+-------------+
1 row in set (0.05 sec)
As soon as we use just a couple of values pruning works:
mysql> explain partitions select * from quarterly_report_status where report_upd
ated
-> in ('2008-07-01 00:00:40','2008-08-01 00:00:40');
+----+-------------+-------------------------+------------+------+--------------
-+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys
| key | key_len | ref | rows | Extra |
+----+-------------+-------------------------+------------+------+--------------
-+------+---------+------+------+-------------+
| 1 | SIMPLE | quarterly_report_status | p3 | ALL | NULL
| NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------------------------+------------+------+--------------
-+------+---------+------+------+-------------+
1 row in set (0.00 sec)
I'd say this is a reasonable feature request to make server more smart when trying to use partition pruning, not just compare number of potential values in the range with number of partitions.
[13 Mar 2011 9:58]
Priyank Patil
On further analysis I found that the pruning limitation - "Number of equalities should be less than number of partitions" and "number of equalities should be small" - does not apply to RANGE partitioning on other data types such as integers.
In my case, where I wanted to partition on TIMESTAMP column named EVENT_TIME, I created another INTEGER column UNIXSECONDS to hold the value of expression UNIX_TIMESTAMP(EVENT_TIME). This column is populated in a TRIGGER before inserting the row into the table.
I made 52 RANGE partitions based on UNIXSECONDS column and it worked as required.
I did not encounter any of the above limitations i.e I could write queries such as
select * from ...... where UNIXSECONDS between UNIX_TIMESTAMP('2011-03-01') and UNIX_TIMESTAMP('2011-03-15')
The number of seconds in 14 days is a lot more than the number of partitions and is also doesn't make for a small set of equalities.
I believe TIMESTAMP is internally stored as a number and it should be possible for MySQL to handle this internally without too many changes.
[29 Apr 2011 9:56]
Mattias Jonsson
Duplicate of bug#28928.
[5 Apr 2013 7:07]
Jian Li
explain partitions works with partition pruning, but select * doesn't return anything.
mysql> select * from arc_event_annotation where manager_receipt_time in ('2013-03-01', '2013-03-20');
Empty set (0.01 sec)
mysql> explain partitions select * from arc_event_annotation where manager_receipt_time in ('2013-03-01'
, '2013-03-20');
+----+-------------+----------------------+------------------------------------------------------------- +------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+------------------------------------------------------------- +------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | arc_event_annotation | ARC_EVENT_ANNOTATION_20130304,ARC_EVENT_ANNOTATION_20130325 | ALL | arc_n_mrt | NULL | NULL | NULL | 2 | Using where |
+----+-------------+----------------------+------------------------------------------------------------- +------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
[5 Apr 2013 7:07]
Jian Li
explain partitions works with partition pruning, but select * doesn't return anything.
mysql> select * from arc_event_annotation where manager_receipt_time in ('2013-03-01', '2013-03-20');
Empty set (0.01 sec)
mysql> explain partitions select * from arc_event_annotation where manager_receipt_time in ('2013-03-01'
, '2013-03-20');
+----+-------------+----------------------+------------------------------------------------------------- +------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+------------------------------------------------------------- +------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | arc_event_annotation | ARC_EVENT_ANNOTATION_20130304,ARC_EVENT_ANNOTATION_20130325 | ALL | arc_n_mrt | NULL | NULL | NULL | 2 | Using where |
+----+-------------+----------------------+------------------------------------------------------------- +------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
[5 Apr 2013 8:37]
Mattias Jonsson
Jian Li, can you please create a new bug for this including a repeatable test case? Including full CREATE TABLE, INSERT and SELECT statements + mysql version used. Another option is to ask for help in the partitioning forum: http://forums.mysql.com/list.php?106
