mysql partition pruning not work

Question

I created a table with MySQL partition by hash(to_days(...)).

CREATE TABLE `requestlog` (
  `remotehost` varchar(40) DEFAULT NULL,
  `user` varchar(255) DEFAULT NULL,
  `request_time_str` varchar(40) DEFAULT NULL,
  `request_time` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `request_line` varchar(255) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `bytes` int(11) DEFAULT NULL,
  `referer` text,
  `useragent` text,
  `host` text,
  `instance` text,
  `ms` int(11) DEFAULT NULL,
  `cpu_ms` int(11) DEFAULT NULL,
  `api_cpu_ms` int(11) DEFAULT NULL,
  `cpm_usd` float DEFAULT NULL,
  `queue_name` varchar(40) DEFAULT NULL,
  `task_name` varchar(40) DEFAULT NULL,
  `loading_request` tinyint(1) DEFAULT NULL,
  `pending_ms` int(11) DEFAULT NULL,
  `exit_code` int(11) DEFAULT NULL,
  `throttle_code` int(11) DEFAULT NULL,
  `method` varchar(40) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  `querystring` text,
  `protocol` varchar(40) DEFAULT NULL,
  `applog` text,
  `applog0` text,
  `applog1` text,
  `applog2` text,
  `applog3` text,
  `applog4` text,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMES
TAMP,
  PRIMARY KEY (`request_time`,`id`),
  UNIQUE KEY `path` (`path`,`request_time`,`remotehost`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
/*!50100 PARTITION BY HASH (to_days(request_time))
PARTITIONS 1000 */

However while I execute the following query. the explain partitions result shows the partition pruning not work because it scans all partitions belong to this table...

explain partitions select count(*) from requestlog where to_days(request_time) = '2012-08-01';

I tried the sample in this article. the explain partitions still shows it scan all partitions. how to partition a table by datetime column?

How to let the partition pruning works? Any hints?

Omesh · Accepted Answer · 2012-08-16T09:31:54.710

1

try this without to_days:

explain partitions select count(*) from requestlog where request_time = '2012-08-01';

EDIT:

explain partitions 
        select count(*) 
        from requestlog 
        where request_time BETWEEN '2012-08-01 00:00:00' AND '2012-08-01 23:59:59';

edited Aug 16 '12 at 09:31

answered Aug 16 '12 at 09:10

Omesh

27,801
6
42
51

the partitions pruning works in this case, but the query itself only return results whose request_time = '2012-08-01 00:00:00' – lucemia Aug 16 '12 at 09:12
that works because you have index on column `request_time`. also `To_DAYS` function returns integer value which you are comparing with a date value. – Omesh Aug 16 '12 at 09:17
the partitions pruning works in this query, but the results are not correct. for example, this query ignore data whose request_time = '2012-08-01 00:00:01' – lucemia Aug 16 '12 at 09:25
try new edited query or you can also use `DATE(request_time) = '2012-08-01'`; – Omesh Aug 16 '12 at 09:32
both `where request_time between ...` and `where date(request_time) = ...` will scan all partitions. I guess MySQL just not support this kind of partition pruning. – lucemia Aug 16 '12 at 09:35
`HASH` will work if you have separate DATE column. you need to create `PARITION BY RANGE` for range to work. If you have MySQL 5.5 then you can create it on TIMESTAMP field also. http://dev.mysql.com/doc/refman/5.5/en/partitioning-range.html – Omesh Aug 16 '12 at 09:44

mysql partition pruning not work

1 Answers1