MySQL Forums
Forum List  »  Performance

Slow Query, Optimization Advice Needed
Posted by: Michael Lawson
Date: April 26, 2016 09:30AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Slow Query, Optimization Advice Needed
1653
April 26, 2016 09:30AM


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.