MySQL Forums
Forum List  »  Partitioning

partition pruning problem
Posted by: tomaz bracic
Date: June 10, 2008 04:47AM

Hi,

I've created table as
CREATE TABLE `stat1` (
`event_id` int(11) DEFAULT NULL,
`user_id` int(11) DEFAULT NULL,
`subscriber_id` int(11) DEFAULT NULL,
`device_id` int(11) DEFAULT NULL,
`content_id` int(11) DEFAULT NULL,
`timestamp` timestamp NULL DEFAULT NULL,
`duration` int(11) DEFAULT NULL,
`rating` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
PARTITION BY RANGE ( to_days(timestamp)) (
partition p0 values less than (733566),
partition p1 values less than (733573),
partition p2 values less than (733580),
partition p3 values less than (733587),
partition p4 values less than (733594),
partition p5 values less than (733601)
);

_____________________________________________________________
inserted few data.... mostly in partition 0.
Just for help....
/*
select to_days('2008-06-08 00:00'); -- 733566
select to_days('2008-06-15 00:00'); -- 733573
select to_days('2008-06-22 00:00'); -- 733580
select to_days('2008-06-29 00:00'); -- 733587
select to_days('2008-07-06 00:00'); -- 733594
select to_days('2008-07-13 00:00'); -- 733601
*/


select distinct(timestamp) from stat1;

2008-06-09 14:11:10 (majority of records are inserted on 9th of june)
2008-06-10 09:43:30
2008-06-25 13:04:59
2008-07-02 13:04:59

select FROM_UNIXTIME(1213014405); -- 2008-06-09 14:26:45
select FROM_UNIXTIME(1213054405); -- 2008-06-10 01:33:25
_______________________________________________________________________

Now if I execute sentence below,


________________________________________________________________________
mysql> explain partitions select distinct(user_id), group_concat(distinct(content_id) separator ',')
-> from stat1 where timestamp between FROM_UNIXTIME(1213014405) and FROM_UNIXTIME(1213054405)
-> and event_id = '1'
-> and content_id is not null
-> 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 | 991593 | Using where; Using filesort |
+----+-------------+-------+-------------------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)



All partitions are searched. (p0,p1,p2,p3,p4,p5) Why's that?
Something is not ok.

I would really appreciate some help.

Kind regards,
Tomaz

Options: ReplyQuote


Subject
Views
Written By
Posted
partition pruning problem
3674
June 10, 2008 04:47AM
3177
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.