MySQL Forums :: Optimizer & Parser :: optimize request with order by and limit


Advanced Search

optimize request with order by and limit
Posted by: yassine laouija ()
Date: July 18, 2013 05:26AM

please help to reduce the time of response of this request (2.8 sec):

SELECT SQL_NO_CACHE trk.TrkTypeID , trk.TrkID , st.Alias

FROM tracking trk
INNER JOIN TrackingEnv trkenv ON trk.TrkID = trkenv.TrkID
INNER JOIN Job jb on jb.JobID=trk.JobID
INNER JOIN statustype st on st.StatusTypeID = jb.StatusTypeID

WHERE trk.TrkTypeID IN ( 2,3,10) AND trkenv.EnvID IN ( 1,1,20 )
Order BY jb.StartDate DESC LIMIT 0 , 10

and the explain on this request give :

select-type | table | type | possible_keys | key | key_len | ref | rows | Extra

SIMPLE | trk | range| PRIMARY,JobID,TrkTypeID | TrkTypeID | 2 | null | 151146 | Using where;Using temporary;Using filesort

SIMPLE | trkenv | ref | TrkID_EnvID | TrkID_EnvID | 4 | base.trk.TrkID | 1 | Using where;Using index

SIMPLE | jb | eq_ref | PRIMARY,StatusTypeID | JobID | 3 | base.st.StatusTypeID | 1 |

SIMPLE | st | eq_ref | PRIMARY | PRIMARY | 4 | base.jb.StatusTypeID | 1 |


these are de tablese's definition :

Job Table :

CREATE TABLE `job` (
`JobID` int(10) unsigned NOT NULL,
`StartDate` datetime NOT NULL,
`EndDate` datetime DEFAULT NULL,
`Duration` int(10) unsigned DEFAULT NULL,
`ParentJobID` int(10) unsigned DEFAULT NULL,
`SessionJobID` int(10) unsigned DEFAULT NULL,
`JobTypeID` smallint(5) unsigned NOT NULL,
`StatusTypeID` mediumint(8) unsigned NOT NULL,
`Visible` tinyint(1) NOT NULL,
`EnvID` int(5) unsigned NOT NULL,
`Description` text,
PRIMARY KEY (`JobID`),
KEY `ParentJobID` (`ParentJobID`),
KEY `SessionJobID` (`SessionJobID`),
KEY `JobTypeID` (`JobTypeID`),
KEY `StatusTypeID` (`StatusTypeID`),
KEY `EnvID` (`EnvID`),
KEY `StartDate` (`StartDate`),
CONSTRAINT `FK_job_environment` FOREIGN KEY (`EnvID`) REFERENCES `environment` (`EnvID`),
CONSTRAINT `FK_job_job` FOREIGN KEY (`ParentJobID`) REFERENCES `job` (`JobID`),
CONSTRAINT `FK_job_jobtype` FOREIGN KEY (`JobTypeID`) REFERENCES `jobtype` (`JobTypeID`),
CONSTRAINT `FK_job_job_2` FOREIGN KEY (`SessionJobID`) REFERENCES `job` (`JobID`),
CONSTRAINT `FK_job_statustype` FOREIGN KEY (`StatusTypeID`) REFERENCES `statustype` (`StatusTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


Tracking Table :

CREATE TABLE `tracking` (
`TrkID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`TrkTypeID` smallint(5) unsigned NOT NULL,
`JobID` int(10) unsigned NOT NULL,
`Visible` tinyint(1) NOT NULL DEFAULT '1',
`EnvID` int(5) unsigned NOT NULL,
`TrkStatusTypeID` mediumint(8) unsigned NOT NULL DEFAULT '0',
`LockUserID` int(10) unsigned DEFAULT NULL,
PRIMARY KEY (`TrkID`),
KEY `JobID` (`JobID`),
KEY `LockUserID` (`LockUserID`),
KEY `TrkStatusTypeID` (`TrkStatusTypeID`),
KEY `EnvID` (`EnvID`),
KEY `TrkTypeID` (`TrkTypeID`),
CONSTRAINT `tracking_ibfk_1` FOREIGN KEY (`TrkTypeID`) REFERENCES `trackingtype` (`TrkTypeID`),
CONSTRAINT `tracking_ibfk_2` FOREIGN KEY (`JobID`) REFERENCES `job` (`JobID`),
CONSTRAINT `tracking_ibfk_4` FOREIGN KEY (`EnvID`) REFERENCES `environment` (`EnvID`),
CONSTRAINT `tracking_ibfk_5` FOREIGN KEY (`TrkStatusTypeID`) REFERENCES `trackingstatustype` (`TrkStatusTypeID`),
CONSTRAINT `tracking_ibfk_6` FOREIGN KEY (`LockUserID`) REFERENCES `user` (`UserID`)
) ENGINE=InnoDB AUTO_INCREMENT=309411 DEFAULT CHARSET=utf8


TrackingEnv Table :

CREATE TABLE `trackingenv` (
`TrkID` int(10) NOT NULL,
`EnvID` int(10) NOT NULL,
`Write` int(10) NOT NULL,
`Read` int(10) NOT NULL,
KEY `TrkID_EnvID` (`TrkID`,`EnvID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

StatusType Table :

CREATE TABLE `statustype` (
`StatusTypeID` mediumint(10) unsigned NOT NULL,
`LabelID` int(10) unsigned DEFAULT NULL,
`Alias` varchar(20) NOT NULL,
PRIMARY KEY (`StatusTypeID`),
UNIQUE KEY `Alias` (`Alias`),
KEY `LabelID` (`LabelID`),
CONSTRAINT `statustype_ibfk_1` FOREIGN KEY (`LabelID`) REFERENCES `label` (`LabelID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Thnaks for your help

Options: ReplyQuote


Subject Views Written By Posted
optimize request with order by and limit 2057 yassine laouija 07/18/2013 05:26AM
Re: optimize request with order by and limit 1140 Rick James 07/20/2013 10:25PM
Re: optimize request with order by and limit 975 yassine laouija 07/22/2013 09:46AM
Re: optimize request with order by and limit 878 Rick James 07/23/2013 08:58AM
Re: optimize request with order by and limit 936 yassine laouija 07/26/2013 04:07AM
Re: optimize request with order by and limit 964 Rick James 07/26/2013 11:53PM
Re: optimize request with order by and limit 954 yassine laouija 07/29/2013 04:40AM
Re: optimize request with order by and limit 952 Rick James 08/01/2013 10:39PM


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.