optimize request with order by and limit
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