Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ;
share|improve this question

1 Answer 1

up vote 1 down vote accepted

What do you mean by "multitable partition" mean? You can partition each table independently.

INT(3) does not mean what you may think.

Learn about "compound" indexes.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.