MySQL Forums
Forum List  »  Partitioning

partition pruning and DIV
Posted by: Bill Moninger
Date: March 26, 2010 11:23AM

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
----------------------------------------

Options: ReplyQuote


Subject
Views
Written By
Posted
partition pruning and DIV
4105
March 26, 2010 11:23AM
1785
April 06, 2010 09:20PM
2099
April 07, 2010 04:35PM
1888
April 07, 2010 05:08PM


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.