Re: Backup and trimming data idea

[newbie Shai]

Dear Rick,
Ok I am going to try out some partition tests. My script is as below is that right script? I am just trying out now for 10 days. So each time I must run the create table script is it? So as the days grows I must keep adding more partitioning right?


CREATE TABLE IF NOT EXISTS `tblMainData` (
`mainDataID` int(11) NOT NULL AUTO_INCREMENT,
`linkID` int(5) NOT NULL,
`header` varchar(3) NOT NULL,
`deviceSerialNumber` varchar(20) NOT NULL,
`latitude` double NOT NULL,
`longitude` double NOT NULL,
`speed` float NOT NULL,
`course` int(3) NOT NULL,
`dateTimer` datetime NOT NULL,
`gpsDateTime` datetime NOT NULL,
`insertDateTime` datetime NOT NULL,
`odoMeter` float NOT NULL DEFAULT '0',
`driverID` int(5) NOT NULL,
`eventAlertID` int(11) NOT NULL DEFAULT '0',
`mainDataInsertDateTime` datetime NOT NULL,
`gpsString` varchar(450) NOT NULL,
PRIMARY KEY (`mainDataID`),
KEY `dateTime` (`dateTimer`),
KEY `linkID` (`linkID`),
KEY `eventAlertID` (`eventAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


PARTITION BY RANGE(gpsDateTime)
(
PARTITION p401 VALUES LESS THAN (TO_DAYS('2012-07-01'),
PARTITION p402 VALUES LESS THAN (TO_DAYS('2012-07-02'),
PARTITION p403 VALUES LESS THAN (TO_DAYS('2012-07-03'),
PARTITION p404 VALUES LESS THAN (TO_DAYS('2012-07-04'),
PARTITION p405 VALUES LESS THAN (TO_DAYS('2012-07-05),
PARTITION p406 VALUES LESS THAN (TO_DAYS('2012-07-06'),
PARTITION p407 VALUES LESS THAN (TO_DAYS('2012-07-07'),
PARTITION p408 VALUES LESS THAN (TO_DAYS('2012-07-08'),
PARTITION p409 VALUES LESS THAN (TO_DAYS('2012-07-09'),
);