MySQL Forums
Forum List  »  Optimizer & Parser

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
2857
July 18, 2013 05:26AM


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.