Query problem with date partitioning
I have a very large table that I've recently partitioned that I'm having issues with partition pruning. My table has a localDateTime field that is a DATETIME. When creating partitions, I used HASH to_days(localDateTime). I've tried some simple queries using the "explain partitions" to see how the pruning works and don't understand why the pruning isn't working the way I think it should.
explain partitions select localDateTime from t1 where localDateTime = '2008-05-22 00:00:00'
1, 'SIMPLE', 't1', 'p16', 'index', '', 'PRIMARY', '8', '', 17883499, 'Using where; Using index'
explain partitions select localDateTime from t1 where localDateTime >= '2008-05-22 00:00:00'
1, 'SIMPLE', 't1', 'p0,p1,p2,p3,p4,p5,p6,p7,p8,p9,p10,p11,p12,p13,p14,p15,p16', 'index', '', 'PRIMARY', '8', '', 280649472, 'Using where; Using index'
Why is the first query able to prune to a single partition while the second can't?
CREATE TABLE `Logs`.`t1` (
`logId` int(10) unsigned NOT NULL AUTO_INCREMENT,
`dbIndex` varchar(45) NOT NULL,
`sGroupId` int(10) unsigned NOT NULL,
`utcDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`localDateTime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`sComputernameId` int(10) unsigned NOT NULL,
`uriId` int(10) unsigned NOT NULL,
`scStatus` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`logId`,`localDateTime`) USING BTREE,
KEY `fk_sGroup` (`sGroupId`),
KEY `fk_sComputername` (`sComputernameId`),
KEY `fk_uri` (`uriId`)
) ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=latin1 PARTITION BY HASH (to_days(localDateTime)) PARTITIONS 17;
Edited 2 time(s). Last edit at 05/22/2008 04:40PM by Mac Soileau.