troubleshooting Question
This query times out. I've incorporated an EXPLAIN, but I don't know how to make use of it's feedback.
Bruce Gust
asked on
asked on
Here's the query:
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?
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:
| 1 | INSERT | tasks_master_tracker_idb | ALL | ||||||||
| 1 | PRIMARY | t | ALL | 76898 | 90.00 | Using where; Using temporary | |||||
| 1 | PRIMARY | tsk | ALL | 3 | 100.00 | Using where; Using join buffer (hash join) | |||||
| 1 | PRIMARY | r | eq_ref | PRIMARY,ID_UNIQUE,INTID__UNIQUE | PRIMARY | 4 | func | 1 | 100.00 | Using where | |
| 1 | PRIMARY | rt | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 100.00 | Using where | |
| 5 | SUBQUERY | tasks_master_tracker_idb | index | TASK_ID_UNIQUE,TASK_ID | TASK_ID_UNIQUE | 5 | 1 | 100.00 | Using index | ||
| 4 | DEPENDENT SUBQUERY | requirements_transport | ALL | 24247 | 10.00 | Using where | |||||
| 3 | DEPENDENT SUBQUERY | requirements | ref | INTID__UNIQUE | INTID__UNIQUE | 4 | idb_dev.t.ID | 1 | 100.00 | Using index | |
| 2 | DEPENDENT SUBQUERY | c | ref | UNIQUE,RID | UNIQUE | 66 | const,idb_dev.t.ID | 3 | 10.00 | Using where; Using index |
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.
Our community of experts have been thoroughly vetted for their expertise and industry experience.