MySQL Forums
Forum List  »  Partitioning

Re: Query problem with date partitioning
Posted by: Phil Hildebrand
Date: May 23, 2008 11:44AM

mysql> alter table t1 partition by hash (to_days(localDateTime)) partitions 6;Query OK, 22 rows affected (0.12 sec)
Records: 22 Duplicates: 0 Warnings: 0

I believe this explains it a little:

http://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html

"...Pruning can be used only on integer columns of tables partitioned by HASH or KEY. ... However, if the table stores year values in an INT column, then a query having WHERE year_col >= 2001 AND year_col <= 2005 can be pruned. ..."



In otherwords, it won't prune between in a hash partition unless it's an integer (it appears to be the same in 6.0). I think this will probably change at some point... (seems like we could evaluate the hash and apply pruning...)

The other issue you have is using a timestamp instead of datetime (need the datetime to prune with between as well)

Example:

mysql> explain partitions select localDateTime from t1 where localDateTime between '2008-05-22 00:00:00' and '2008-05-23 04:00:00' ;
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | p0,p1,p2,p3,p4,p5 | index | NULL | PRIMARY | 12 | NULL | 22 | Using where; Using index |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

Modify to datetime, with hash it still scans all partitions because it's not 'really' an int:

mysql> alter table t1 modify column localdatetime datetime;
Query OK, 22 rows affected (0.07 sec)
Records: 22 Duplicates: 0 Warnings: 0

mysql> explain partitions select localDateTime from t1 where localDateTime between '2008-05-22 00:00:00' and '2008-05-23 04:00:00' ;
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | p0,p1,p2,p3,p4,p5 | index | NULL | PRIMARY | 12 | NULL | 22 | Using where; Using index |
+----+-------------+-------+-------------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

Modify to a range partition, and then the between works:

mysql> alter table t1 PARTITION BY range (to_days(localDateTime))
( partition p1 values less than (to_days('2008-05-25')),
partition p2 values less than (to_days('2008-05-27')),
partition p3 values less than (to_days('2008-05-29')),
partition p4 values less than (to_days('2008-05-31')),
partition p5 values less than maxvalue );
Query OK, 22 rows affected (0.09 sec)
Records: 22 Duplicates: 0 Warnings: 0

mysql> explain partitions select localDateTime from t1 where localDateTime between '2008-05-22 00:00:00' and '2008-05-23 04:00:00' ;+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | p1 | index | NULL | PRIMARY | 12 | NULL | 7 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

Modify to timestamp, however, and we're back to scanning all partitions:

mysql> alter table t1 modify column localdatetime timestamp;Query OK, 22 rows affected (0.09 sec)
Records: 22 Duplicates: 0 Warnings: 0

mysql> explain partitions select localDateTime from t1 where localDateTime between '2008-05-22 00:00:00' and '2008-05-23 04:00:00' ;
+----+-------------+-------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | p1,p2,p3,p4,p5 | index | NULL | PRIMARY | 8 | NULL | 23 | Using where; Using index |
+----+-------------+-------+----------------+-------+---------------+---------+---------+------+------+--------------------------+
1 row in set (0.00 sec)


I tested this with 5.1.24 and 6.0, so the same limitations exist there with respect to hash partitions vs range partitions and between pruning. I think it's a reasonable feature request, however... seems we should be able to evaluate the hash of to_days(some date) quickly in the optimizer for purposes of partition pruning. Maybe it's a work item in forge ?

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Query problem with date partitioning
3343
May 23, 2008 11:44AM


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.