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:
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)
| 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 |
MySQL Server
Last Comment
Cyril Joudieh
SOLUTION
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.
ASKER CERTIFIED SOLUTION
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.
SOLUTION
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.
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...
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?