Avatar of Bruce Gust
Bruce Gust
Flag for United States of America asked on

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

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)

Open in new window

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?
MySQL Server

Avatar of undefined
Last Comment
Cyril Joudieh

8/22/2022 - Mon
SOLUTION
Bembi

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Bruce Gust

ASKER
The SELECT is sound. When I run it, it will give me the standard 100 rows without much delay at all.

When I first ran it in context, the table that I was attempting to INSERT rows into had over 100K records. I thought that might be a problem given the subquery : AND t.ID not in (select TASK_ID from tasks_master_tracker_idb) , but that didn't seem to have any impact.

I tried this in an attempt to figure out how may records I was attempting to INSERT...

select count(*) from (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,
      t.CREATE_DT,
      t.UPDATED_BY,
       t.LAST_MODIFIED_DT
      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))

Open in new window

...and I got an error that said, "Every derived table must have it's own alias," which doesn't make sense since the SELECT that I'm counting from can be run independently.

What do you think?
ASKER CERTIFIED SOLUTION
Bembi

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Cyril Joudieh

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck