MySQL Forums
Forum List  »  Partitioning

Re: partition pruning problem
Posted by: Phil Hildebrand
Date: June 10, 2008 11:41PM

I believe thats because between will only prune partitions on date or datetime, not timestamp:

mysql> explain partitions select distinct(user_id), group_concat(distinct(content_id) separator ',')
from stat1 where timestamp between
FROM_UNIXTIME(1213014405) and FROM_UNIXTIME(1214054405)
group by user_id;

+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | stat1 | p0,p1,p2,p3,p4,p5 | ALL | NULL | NULL | NULL | NULL | 6 | Using where; Using filesort |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> alter table stat1 modify timestamp datetime;

mysql> explain partitions select distinct(user_id), group_concat(distinct(content_id) separator ',')
from stat1 where timestamp between
FROM_UNIXTIME(1213014405) and FROM_UNIXTIME(1214054405)
group by user_id;

+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | stat1 | p1,p2 | ALL | NULL | NULL | NULL | NULL | 2 | Using where; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------+
1 row in set (0.00 sec)

mysql>


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

Most likely it's due to how the timestamp datatype works. This will likely be changed in future versions...

Options: ReplyQuote


Subject
Views
Written By
Posted
3674
June 10, 2008 04:47AM
Re: partition pruning problem
3176
June 10, 2008 11:41PM
2376
June 11, 2008 04:01AM


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.