Slow Query, Optimization Advice Needed
I am on a shared server with a query timeout of 20 seconds. This query keeps exceeding that. I don't know if it is a result of the query/database, the hosting, or both. I inherited the system and am trying to do my best with it. Many days it works fine, but once the system starts timing out certain SQL calls it generally can go for hours without working.
I'm sure it has to do with tblMovements.
select count(0) AS nCount, L.nLocationID, L.nCapacity, TV.nSiteID
from tblLocations L
join tblMovements M on L.nLocationID = M.nLocationID
join tblTrailers T on M.nTrailerID = T.nTrailerID
join tblTrailerLogs TL on TL.nTrailerID = T.nTrailerID
join tblTrailerVendors TV on TV.nTrailerVendorID = T.nTrailerVendorID
where TL.nMovementOutID = 0
and T.eStatus = 'active'
and not M.nTrailerID in (select M2.nTrailerID from tblMovements M2 where M2.tsMovement > M.tsMovement)
group by L.nLocationID
tblLocations has 298 records
tblMovements has 393,313 records
tblTrailers has 47,036 records
tblTrailerLogs has 78,397 records
tblTrailerVendors has 199 records
Database table designs:
CREATE TABLE `tblLocations` (
`nLocationID` int(11) NOT NULL AUTO_INCREMENT,
`nSiteID` tinyint(4) NOT NULL,
`strLocationAbbrev` varchar(5) NOT NULL,
`strLocationName` varchar(50) NOT NULL,
`nCapacity` mediumint(5) unsigned DEFAULT NULL,
`eCheckpoint` enum('Checkin','Checkout') NOT NULL,
`eStatus` enum('active','inactive') NOT NULL DEFAULT 'active',
PRIMARY KEY (`nLocationID`),
KEY `strLocationAbbrev` (`strLocationAbbrev`),
KEY `nSiteID` (`nSiteID`),
KEY `strLocationName` (`strLocationName`)
) ENGINE=InnoDB AUTO_INCREMENT=315 DEFAULT CHARSET=latin
CREATE TABLE `tblMovements` (
`nMovementID` int(11) NOT NULL AUTO_INCREMENT,
`nUserID` int(11) NOT NULL,
`nDriverID` int(11) NOT NULL,
`nYardDogID` int(11) unsigned DEFAULT NULL,
`nMaterialID` int(11) NOT NULL,
`eMovementType` enum('Pickup','Delivery','Dispatch','Yard Dog') NOT NULL,
`nTrailerID` int(11) NOT NULL,
`tsMovement` timestamp NULL DEFAULT NULL,
`nShipperID` int(10) unsigned NOT NULL,
`strLoadNumber` varchar(20) NOT NULL,
`nWeight` int(11) NOT NULL,
`nLocationID` int(11) NOT NULL,
`txtNotes` text,
`bSealInstalled` tinyint(1) DEFAULT '0',
`tsClaimed` timestamp NULL DEFAULT NULL,
`tsComplete` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`nMovementID`),
KEY `tsClaimed` (`tsClaimed`),
KEY `tsComplete` (`tsComplete`),
KEY `tsMovement` (`tsMovement`),
KEY `strLoadNumber` (`strLoadNumber`),
KEY `nShipperID` (`nShipperID`),
KEY `nYardDogID` (`nYardDogID`),
KEY `nTrailerID` (`nTrailerID`),
KEY `nLocationID` (`nLocationID`),
KEY `eMovementType` (`eMovementType`),
KEY `nMaterialID` (`nMaterialID`),
KEY `nDriverID` (`nDriverID`),
KEY `nUserID` (`nUserID`)
) ENGINE=InnoDB AUTO_INCREMENT=393349 DEFAULT CHARSET=latin1
CREATE TABLE `tblTrailers` (
`nTrailerID` int(11) NOT NULL AUTO_INCREMENT,
`nTrailerTypeID` int(11) NOT NULL DEFAULT '20',
`nTrailerVendorID` int(11) NOT NULL,
`strTrailerNumber` varchar(20) NOT NULL,
`bLocked` tinyint(1) NOT NULL,
`bLoaded` tinyint(1) NOT NULL DEFAULT '0',
`tsCreated` timestamp NULL DEFAULT NULL,
`eStatus` enum('active','inactive') NOT NULL DEFAULT 'active',
PRIMARY KEY (`nTrailerID`),
KEY `nTrailerTypeID` (`nTrailerTypeID`,`nTrailerVendorID`),
KEY `nTrailerVendorID` (`nTrailerVendorID`),
KEY `eStatus` (`eStatus`),
KEY `nTrailerTypeID_nTrailerVendorID` (`nTrailerTypeID`)
) ENGINE=InnoDB AUTO_INCREMENT=48611 DEFAULT CHARSET=latin1
CREATE TABLE `tblTrailerLogs` (
`nTrailerLogID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`nTrailerID` int(11) NOT NULL,
`nMovementInID` int(11) NOT NULL,
`nMovementOutID` int(11) NOT NULL,
PRIMARY KEY (`nTrailerLogID`),
KEY `nMovementOutID` (`nMovementOutID`),
KEY `nMovementInID` (`nMovementInID`),
KEY `nTrailerID_nMovementInID_nMovementOutID` (`nTrailerID`,`nMovementInID`,`nMovementOutID`),
KEY `nTrailerID` (`nTrailerID`)
) ENGINE=InnoDB AUTO_INCREMENT=84830 DEFAULT CHARSET=latin1
CREATE TABLE `tblTrailerVendors` (
`nTrailerVendorID` int(11) unsigned NOT NULL AUTO_INCREMENT,
`nSiteID` tinyint(4) NOT NULL,
`strTrailerVendorName` varchar(50) NOT NULL,
`nDetentionMinutes` int(11) NOT NULL,
`tsCreated` timestamp NULL DEFAULT NULL,
`eStatus` enum('active','inactive') DEFAULT 'active',
PRIMARY KEY (`nTrailerVendorID`),
KEY `nDetentionMinutes` (`nDetentionMinutes`),
KEY `strTrailerVendorName` (`strTrailerVendorName`),
KEY `nSiteID` (`nSiteID`)
) ENGINE=InnoDB AUTO_INCREMENT=231 DEFAULT CHARSET=latin1