Re: Time Manipulation In Query
Dear Rick,
I have added the index on dateTimer and below is my tblScvData structure. My problem now is that my tblAssociate is linked to tblScvData on one to many basis. So when I run my query I get all the tblScvData. In my query I just want the tblAssociate data where there is data in tblScvData in the range. I have manage to limit via the distinct but it takes around 20 seconds even on the mysql client. Any performance improvement or rewrite my query?
CREATE TABLE IF NOT EXISTS `tblScvData` (
`scvDataID` int(11) NOT NULL auto_increment,
`header` varchar(3) NOT NULL,
`deviceType` varchar(3) NOT NULL,
`eventID` varchar(2) NOT NULL,
`masterSerialNumber` varchar(20) NOT NULL,
`simStatus` smallint(6) NOT NULL,
`csq` smallint(6) NOT NULL,
`mcc` smallint(6) NOT NULL,
`mccc` smallint(6) 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,
`gpsStat` tinyint(4) NOT NULL,
`unitStat` varchar(12) NOT NULL,
`serialKey` varchar(8) NOT NULL,
`digitalInOut` varchar(4) NOT NULL,
`fuelLevel` varchar(3) NOT NULL,
`battVolt` float NOT NULL,
`deviceVolt` float NOT NULL,
`odoMeter` float NOT NULL default '0',
`chkSum` varchar(8) NOT NULL,
`driverID` int(5) NOT NULL,
`groupID` varchar(8) NOT NULL,
`associateID` int(5) NOT NULL default '0',
`geoFenceInID` int(5) NOT NULL default '0',
`geoFenceOutID` int(11) NOT NULL default '0',
`geoFenceAlertIDIn` int(11) NOT NULL default '0',
`geoFenceAlertIDOut` int(11) NOT NULL default '0',
`eventAlertID` int(11) NOT NULL default '0',
`scvDataInsertDateTime` datetime NOT NULL,
`gpsString` varchar(450) NOT NULL,
PRIMARY KEY (`scvDataID`),
KEY `dateTime` (`dateTimer`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=30890 ;
Subject
Written By
Posted
Re: Time Manipulation In Query
October 25, 2011 11:26PM
November 01, 2011 07:35PM
November 21, 2011 11:50PM
November 23, 2011 11:27AM
November 24, 2011 11:39AM
November 24, 2011 12:11PM
November 25, 2011 12:24PM
November 26, 2011 01:54AM
November 26, 2011 10:44PM
November 27, 2011 04:15AM
November 28, 2011 11:09AM
November 28, 2011 07:52PM
November 29, 2011 09:02PM
November 30, 2011 07:29AM
November 20, 2011 11:46PM