partition pruning problem
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