troubleshooting Question

This query times out. I've incorporated an EXPLAIN, but I don't know how to make use of it's feedback.

Avatar of Bruce Gust
Bruce GustFlag for United States of America asked on
MySQL Server
4 Comments3 Solutions9 ViewsLast Modified:
Here's the query:

INSERT into tasks_master_tracker_idb (`TASK_ID`,   
        `CUSTID`,  
        `INTEGRATION_ID`,  
        `INTEGRATION_NAME`,    
        `INTEGRATION_DESCRIPTION`, 
        `INTEGRATION_TYPE`,    
        `DIRECTION`,   
        `CRITICAL_CLIENTS`,    
        `CRITICAL_OPERATIONS`, 
        `COMPLEXITY`,  
        `COUNTRY_REGION`,  
        `VENDOR`,  
        `INTEGRATION_DOMAIN`,  
        `INTEGRATION_STATUS`,  
        `SCHEDULED_MANUAL`,    
        `SCHEDULE_FREQUENCY`,  
        `MONDAY`,  
        `TUESDAY`, 
        `WEDNESDAY`,   
        `THURSDAY`,    
        `FRIDAY`,  
        `SATURDAY`,    
        `SUNDAY`,  
        `SCHEDULE_NOTES`,  
        `INTEGRATION_RUN_TIME`,    
        `TIME_ZONE`,   
        `TRANSPORT_METHOD`,    
        `PROD_INBOUND_TRANSPORT_ADDR`, 
        `PROD_INBOUND_TRANSPORT_DIR`,  
        `PROD_INBOUND_USER_ID`,    
        `PROD_INBOUND_DELETE_AFTER_RETRIEVAL`,
        `PROD_OUTBOUND_TRANSPORT_ADDR`,    
        `PROD_OUTBOUND_TRANSPORT_DIR`, 
        `PROD_OUTBOUND_USER_ID`,
        `IMPL_INBOUND_TRANSPORT_ADDR`, 
        `IMPL_INBOUND_TRANSPORT_DIR`,  
        `IMPL_INBOUND_USER_ID`,    
        `IMPL_INBOUND_DELETE_AFTER_RETRIEVAL`,
        `IMPL_OUTBOUND_TRANSPORT_ADDR`,    
        `IMPL_OUTBOUND_TRANSPORT_DIR`, 
        `IMPL_OUTBOUND_USER_ID`,
        `FULLFILE_DELTAFILE`,  
        `PROD_INBOUND_FILE_NAME`,  
        `PROD_OUTBOUND_FILE_NAME`, 
        `IMPL_INBOUND_FILE_NAME`,  
        `IMPL_OUTBOUND_FILE_NAME`, 
        `PROD_INBOUND_FILE_DOCUMENT_RETENTION_POLICY`, 
        `IMPL_INBOUND_FILE_DOCUMENT_RETENTION_POLICY`,
        `CLIENT_CONTACT`,  
        `VENDOR_CONTACT`,
        `CREATED_BY`,  
        `CREATED_DATE`,    
        `MODIFIED_BY`, 
        `MODIFIED_DATE`)
      SELECT 
                t.ID,
                t.CUSTID, 
                t.INTID,  
                t.NAME, 
                t.DESCRIPTION,
                r.MAIN_6,
                t.DIRECTION,
                t.PRIORITY,
                tsk.CRITICAL_TO_OPERATION, 
                t.COMPLEXITY,  
                t.COUNTRY,
                r.MAIN_1,
                r.MAIN_7,
                r.REQ_STATUS,
                r.TECH_21,
                r.TECH_5,
                tsk.MONDAY, 
                tsk.TUESDAY, 
                tsk.WEDNESDAY, 
                tsk.THURSDAY, 
                tsk.FRIDAY, 
                tsk.SATURDAY, 
                tsk.SUNDAY,
                r.TECH_29,
                r.TECH_20,
                r.TECH_20,
                rt.TRANSPORT_3,
                rt.TRANSPORT_4,
                rt.TRANSPORT_21,
                rt.TRANSPORT_5,
                rt.TRANSPORT_26,
                rt.TRANSPORT_4,
                rt.TRANSPORT_21,
                rt.TRANSPORT_5,
                rt.TRANSPORT_9,
                rt.TRANSPORT_22,
                rt.TRANSPORT_10,
                rt.TRANSPORT_26,
                rt.TRANSPORT_9,
                rt.TRANSPORT_22,
                rt.TRANSPORT_10,
                r.DATA_6,
                rt.TRANSPORT_18,                
            rt.TRANSPORT_18, 
                rt.TRANSPORT_19, 
                rt.TRANSPORT_19, 
                rt.TRANSPORT_24, 
                rt.TRANSPORT_24, 
                 (SELECT c.USER_ID FROM contacts c where c.RID=t.ID and c.ROLE='PM' and RTYPE='TASK' LIMIT 1), 
                t.VENDOR,
                t.CREATED_BY,
               case
               when 
                  t.CREATE_DT IS NULL
               then
                  CURDATE()
               else
                  t.CREATE_DT
            end,
            t.UPDATED_BY,
            case
               when 
                  t.LAST_MODIFIED_DT IS NULL
               then
                  CURDATE()
               else
                  t.LAST_MODIFIED_DT
            end   
                FROM tasks t 
                LEFT JOIN tasks_ams tsk ON t.ID = tsk.RID   
            LEFT JOIN requirements r on t.ID = r.INTID_ and r.ID = (select MAX(ID) from requirements where INTID_ = t.ID)
            LEFT JOIN requirements_transport rt ON t.ID = rt.INTID__ and rt.ID = (select MAX(ID) from requirements_transport where INTID__ = t.ID)
                WHERE  (t.INTID is not null and t.CUSTID is not null ) 
            AND t.ID not in (select TASK_ID from tasks_master_tracker_idb)
When I place "EXPLAIN" before the INSERT, I get this:

1INSERTtasks_master_tracker_idb
ALL






1PRIMARYt
ALL



7689890.00Using where; Using temporary
1PRIMARYtsk
ALL



3100.00Using where; Using join buffer (hash join)
1PRIMARYr
eq_refPRIMARY,ID_UNIQUE,INTID__UNIQUEPRIMARY4func1100.00Using where
1PRIMARYrt
eq_refPRIMARYPRIMARY4func1100.00Using where
5SUBQUERYtasks_master_tracker_idb
indexTASK_ID_UNIQUE,TASK_IDTASK_ID_UNIQUE5
1100.00Using index
4DEPENDENT SUBQUERYrequirements_transport
ALL



2424710.00Using where
3DEPENDENT SUBQUERYrequirements
refINTID__UNIQUEINTID__UNIQUE4idb_dev.t.ID1100.00Using index
2DEPENDENT SUBQUERYc
refUNIQUE,RIDUNIQUE66const,idb_dev.t.ID310.00Using where; Using index
What does this mean and what can I do to streamline the query so I'm not taxing the database to the point where it times out?
ASKER CERTIFIED SOLUTION
Bembi
CEO

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 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 3 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