We have a main table called tblLink in it will have a date field.The tblLink will have its primary key as foreign in few other tables too and one of the table will tblMainData which over time will be in million of rows. So we would like to maintain only 3 months of data. The rest we would like to purge and keep it to generate history reports. The problem all over the google just talk about a single table partition but never talked about multi table partition. So how work out my problem below are the create table and sample queries too.
Some of the queries.
Query 1
SELECT DISTINCT tblLocationFrom.geoFenceName As locationFrom, tblLocationTo.geoFenceName As locationTo,
tblLink.linkID,CAST(Date_Add(tblLink.dateTimeStartJourney, Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimeStartJourney,
CAST(Date_Add(tblLink.dateTimeEndJourney, Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimeEndJourney1,CAST(Date_Add(CAST(Date_Add(tblLink.dateTimeEnd, Interval '-08:00' hour_minute) AS CHAR) , Interval '".$gmtValue."' hour_minute) AS CHAR) As dateTimeEndJourney2,
FROM tblLink
JOIN tblGeoFence AS tblLocationFrom ON tblLink.locationFromID = tblLocationFrom.geoFenceID
JOIN tblGeoFence AS tblLocationTo ON tblLink.locationToID = tblLocationTo.geoFenceID
WHERE (dateTimeStartJourney between '".$b. "' And '".$e."' And tblLink.compID=$cID
Query 2
SELECT tblGeoFence.geoFenceName, CAST(Date_Add(tblEventAlert.eventAlertDateTime , Interval '".$gmtValue."' hour_minute) AS CHAR) As eventAlertDateTime
FROM tblEventAlert
LEFT JOIN tblGeoFence ON tblEventAlert.geoFenceID=tblGeoFence.geoFenceID
WHERE tblEventAlert.link=".$lID." Order By tblEventAlert.eventAlertDateTime Asc";
Create table statements.
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,
`linkID` int(11) NOT NULL DEFAULT '0',
`mainDataID` int(5) NOT NULL,
`vehicleID` int(5) NOT NULL,
`geoFenceID` int(5) NOT NULL,
`eventAlertDateTime` datetime 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 ;
