?
Solved

MySql Timing Out

Posted on 2011-10-17
4
Medium Priority
?
209 Views
Last Modified: 2012-05-12
The attached query is timing out.  When it runs, it should return about 130 records.  My host is telling me that the problem is with the query and I should make it more efficient.  Any suggestions on how to do that?  Thanks!
SELECT loanKey,branchName,auditTypeName,trackingDate,lastName,firstName, loanNumber,loanTypeName,CONCAT(uwLastName,', ',uwFirstName),CONCAT(loLastName,', ',loFirstName),CONCAT(processorLastName,', ',processorFirstName),
   loanFindingKey,findingCategoryName,CONCAT(findingCategoryNumber,'-',findingKey) AS Code, 
CASE WHEN cuS.severitydesc IS NULL THEN stS.severitydesc ELSE cuS.severitydesc END AS currSev,
CASE WHEN finding.redFlag='1' THEN 'Red Flag' ELSE '' END AS RedFlag,
CASE WHEN findingVerbiage IS NULL THEN verbiageCustom WHEN verbiageCustom IS NULL THEN findingVerbiage ELSE CONCAT(findingVerbiage,' ',verbiageCustom) END AS finding
FROM loan
JOIN branch USING (branchKey)
JOIN borrower USING (loanKey)
JOIN audit USING (loanKey)
JOIN auditType USING (auditTypeKey)
JOIN loanType USING (loanTypeKey)
JOIN loanFinding USING (auditID)
JOIN severity stS ON stS.severityKey=loanFinding.stSeverity
LEFT JOIN severity cuS ON cuS.severityKey=loanFinding.customSeverity
JOIN finding USING (findingKey)
JOIN findingCategory USING (findingCategoryKey)
WHERE branchKey='90' 
AND borrower.type='1'
AND borrower.active='1'
AND loan.active='1' 
AND audit.auditTypeKey='1' 
AND loanFinding.active='1'
AND audit.trackingDate
BETWEEN '2011-07-01' AND '2011-07-31'
ORDER BY lastName, firstName, Code ASC

Open in new window

0
Comment
Question by:rcowen00
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:johanntagle
ID: 36983762
Please post the output of EXPLAIN for your query i.e. run:

EXPLAIN SELECT loanKey,branchName,auditTypeName,trackingDate,lastName,firstName, loanNumber,loanTypeName,CONCAT(uwLastName,', ',uwFirstName),CONCAT(loLastName,', ',loFirstName),CONCAT(processorLastName,', ',processorFirstName),
   loanFindingKey,findingCategoryName,CONCAT(findingCategoryNumber,'-',findingKey) AS Code,
CASE WHEN cuS.severitydesc IS NULL THEN stS.severitydesc ELSE cuS.severitydesc END AS currSev,
CASE WHEN finding.redFlag='1' THEN 'Red Flag' ELSE '' END AS RedFlag,
CASE WHEN findingVerbiage IS NULL THEN verbiageCustom WHEN verbiageCustom IS NULL THEN findingVerbiage ELSE CONCAT(findingVerbiage,' ',verbiageCustom) END AS finding
FROM loan
JOIN branch USING (branchKey)
JOIN borrower USING (loanKey)
JOIN audit USING (loanKey)
JOIN auditType USING (auditTypeKey)
JOIN loanType USING (loanTypeKey)
JOIN loanFinding USING (auditID)
JOIN severity stS ON stS.severityKey=loanFinding.stSeverity
LEFT JOIN severity cuS ON cuS.severityKey=loanFinding.customSeverity
JOIN finding USING (findingKey)
JOIN findingCategory USING (findingCategoryKey)
WHERE branchKey='90'
AND borrower.type='1'
AND borrower.active='1'
AND loan.active='1'
AND audit.auditTypeKey='1'
AND loanFinding.active='1'
AND audit.trackingDate
BETWEEN '2011-07-01' AND '2011-07-31'
ORDER BY lastName, firstName, Code ASC;
0
 

Author Comment

by:rcowen00
ID: 36983779
results of Explain
0
 
LVL 24

Accepted Solution

by:
johanntagle earned 2000 total points
ID: 36983802
Ensure there are indexes to borrower.loankey and loanfinding.auditid and audit.trackingDate.  See if that improves things
0
 

Author Closing Comment

by:rcowen00
ID: 36983827
Fantastic.  Thank you
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month17 days, 10 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question