MySQL Forums
Forum List  »  Partitioning

Query problem with date partitioning
Posted by: Mac Soileau
Date: May 22, 2008 04:39PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Query problem with date partitioning
4195
May 22, 2008 04:39PM


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.