Announcement

Announcement Module
Collapse
No announcement yet.

Mysql data partioning and trimming

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Mysql data partioning and trimming

    Our main table, tblLink, will have its primary key as foreign in a few other tables too and one of the tables will be tblMainData which over time will be millions of rows.

    We would like to maintain only 3 months of data based on the insert date in tblLink.

    The rest we would like to purge and keep to generate history reports. What is the best mechanism to go about in this scenario? Below is how our table structures looks like.



    CREATE TABLE IF NOT EXISTS `tblLink` ( `linkID` int(5) NOT NULL, `compID` int(5) NOT NULL, `vehicleID` int(5) NOT NULL, `deviceID` int(5) NOT NULL, `locationFromID` int(5) NOT NULL, `locationToID` int(5) NOT NULL, `employeeIDInsert` int(5) NOT NULL, `dateTimeInsert` datetime NOT NULL, `dateTimeStartJourney` datetime NOT NULL, `dateTimeEnd` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `dateTimeEndJourney` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `linkStatus` enum('a','d','e','m') NOT NULL, PRIMARY KEY (`linkID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;CREATE TABLE IF NOT EXISTS `tblEmailLog` ( `emailLogID` int(11) NOT NULL AUTO_INCREMENT, `compID` smallint(6) NOT NULL, `linkID` int(11) NOT NULL DEFAULT '0', `userID` smallint(6) NOT NULL, `alertCodeID` tinyint(4) NOT NULL, `eventAlertID` int(11) NOT NULL, `userEmail` varchar(100) NOT NULL, `alertDateTime` datetime NOT NULL, `alertInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`emailLogID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;CREATE TABLE IF NOT EXISTS `tblEventAlert` ( `eventAlertID` int(11) NOT NULL AUTO_INCREMENT, `compID` int(5) NOT NULL, `mainDataID` int(5) NOT NULL, `vehicleID` int(5) NOT NULL, `eventAlertSentEmail` varchar(50) DEFAULT NULL, `eventAlertMessage` varchar(255) NOT NULL, PRIMARY KEY (`eventAlertID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;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 ;CREATE TABLE IF NOT EXISTS `tblSubData` ( `subDataID` int(11) NOT NULL AUTO_INCREMENT, `mainDataID` int(11) NOT NULL, `linkID` int(11) NOT NULL, `eventAlertID` int(11) NOT NULL, `deviceSerialNumber` varchar(20) NOT NULL, `subdeviceSerialNumber` varchar(20) NOT NULL, `dateTimer` datetime NOT NULL, `eventType` varchar(2) NOT NULL PRIMARY KEY (`subDataID`), KEY `mainDataID` (`mainDataID`), KEY `linkID` (`linkID`), KEY `eventAlertID` (`eventAlertID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Working...
X