MySQL Forums
Forum List  »  General

Re: Time Manipulation In Query
Posted by: newbie Shai
Date: October 25, 2011 11:26PM

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 ;

Options: ReplyQuote


Subject
Written By
Posted
October 23, 2011 03:13PM
October 24, 2011 10:26AM
October 25, 2011 09:22AM
October 25, 2011 05:26PM
Re: Time Manipulation In Query
October 25, 2011 11:26PM
October 26, 2011 09:31PM
October 26, 2011 08:24PM
October 28, 2011 11:27PM
October 30, 2011 12:28PM
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


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.