troubleshooting Question

Why does this SELECT take so long?

Avatar of Bruce Gust
Bruce GustFlag for United States of America asked on
MySQL Server
4 Comments4 Solutions31 ViewsLast Modified:
I realize this may be vague, but I'm hoping there may be something that stands out in the eyes of someone who knows what to look for, as far as why this SELECT is taking so long to run;

Here's the SQL:

SELECT t.ID AS `*ID`, t.SERVICE_REQ_NO AS `Alternate Tracking ID`, t.INTID AS `Task ID`, t.NAME AS `Task Name`, t.DISCOVERY_NOTES AS `hQuick Notes`, 
  (SELECT cfg1.DESCR FROM idb_cfg.config cfg1 WHERE cfg1.object = 'TASK_TYPE' AND t.TASK_TYPE = cfg1.VALUE limit 1) 
  AS `Task Type`, 
  (SELECT cfg2.DESCR FROM idb_cfg.config cfg2 WHERE cfg2.object = 'SUB_TYPE' AND t.SUB_TYPE = cfg2.VALUE limit 1) AS `Sub Type`, 
  t.PRIORITY AS `Priority`, t.STATUS, t.FAST_TRACK AS `Fast Track`, t.PHASE, (SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) 
  FROM users u WHERE u.ID = t.ASSIGNED_TO limit 1) AS `Assigned To`, 
  (SELECT cfg3.DESCR FROM idb_cfg.config cfg3 WHERE cfg3.object = 'FUNCTIONAL_AREA' 
  AND t.FUNCTIONAL_AREA = cfg3.VALUE limit 1) AS `Functional Area`, 
  t.FUNDING_CATEGORY AS `Funding Category`, 
  t.RELEASE_DATE AS `Release Date`, 
  t.TARGET_RELEASE_DATE AS `Target Release Date`, 
  t.ROM_HOURS_LOW AS `ROM Hours Low`, 
  t.ROM_HOURS_HIGH AS `ROM Hours High`, 
  p.WD_PROJECT_ID AS `Project Code`, 
  (select concat(usr.FIRST_NAME, ' ', usr.LAST_NAME) FROM resources r JOIN projects p on r.PROJID = p.ID JOIN tasks tsk on tsk.PROJECT = p.ID 
  LEFT JOIN users usr on r.USER_ID = usr.ID 
  where r.ROLE='CFGCONS' 
  and tsk.INTID=t.INTID order by r.ASSIGN_DATE DESC LIMIT 1) AS `Consultant`, 
  t.ACT_ID FROM tasks t JOIN projects p
ON t.PROJECT = p.ID
where t.CUSTID = 'ACM01'
  AND CASE WHEN '' in ('2751')
   or 'All' in ('2751') THEN p.type in ('COMPCR' , 'COMPBAU') ELSE t.PROJECT in ('2751') END 
On a whim, I removed this piece:

(select concat(usr.FIRST_NAME, ' ', usr.LAST_NAME) FROM resources r JOIN projects p on r.PROJID = p.ID JOIN tasks tsk on tsk.PROJECT = p.ID
  LEFT JOIN users usr on r.USER_ID = usr.ID
  where r.ROLE='CFGCONS'
  and tsk.INTID=t.INTID order by r.ASSIGN_DATE DESC LIMIT 1) AS `Consultant`,

The difference was dramatic. When I ran it initially in MySQL Workbench, it timed out. When I removed that one subquery, it ran like the wind.

What is it about that subquery that I could improve that would streamline things a bit?



SOLUTION
David Favor
Fractional CTO
Join our community to see this answer!
Unlock 4 Answers and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 4 Answers and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros