partition pruning and DIV
I have a large table that uses unix time (seconds since 1/1/1970) as the date variable (fewer bytes than the DATE type.
I've partitioned it by weeks since 1/1/1970 using
PARTITION BY LIST (time DIV 604800)
This works fine. However, when I do a select using a time range, as shown below, partition pruning doesn't occur.
Any suggestions how to get around this? I realize I could add a column: 'weeks_since_1970', but I'd rather avoid that if I can.
-Bill
Create Table: CREATE TABLE `Bak13` (
`sta_id` mediumint(8) unsigned NOT NULL,
`fcst_len` tinyint(3) unsigned NOT NULL,
`time` int(10) unsigned NOT NULL,
`temp` smallint(6) NOT NULL,
`dp` smallint(6) NOT NULL,
`wd` smallint(5) unsigned NOT NULL,
`ws` smallint(5) unsigned NOT NULL,
UNIQUE KEY `id_time` (`sta_id`,`time`,`fcst_len`),
UNIQUE KEY `time_id` (`time`,`sta_id`,`fcst_len`),
KEY `time` (`time`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY LIST (time DIV 604800)
(PARTITION p2050 VALUES IN (2050) ENGINE = MyISAM,
PARTITION p2051 VALUES IN (2051) ENGINE = MyISAM,
etc
------------------------------
mysql> explain partitions
-> select count(ws) as N
-> from Bak13
-> where 1=1
-> and fcst_len = 1
-> and time <= 1268179200
-> and time < 1268784006
-> \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: Bak13
partitions: p2050,p2051,p2052,p2053,p2054,p2055,p2056,p2057,p2058,p2059,p2060,p2061,p2062,p2063,p2064,p2065,p2066,p2067,p2068,p2069,p2070,p2071,p2072,p2073,p2074,p2075,p2076,p2077,p2078,p2079,p2080,p2081,p2082,p2083,p2084,p2085,p2086,p2087,p2088,p2089,p2090,p2091,p2092,p2093,p2094,p2095,p2096,p2097,p2098,p2099,p2100,p2101,p2102,p2103,p2104
type: range
possible_keys: time_id,time
key: time_id
key_len: 4
ref: NULL
rows: 55
Extra: Using where
----------------------------------------