- 12 Feb 2018, 23:12
#3524
Slow query occurs on candidate page ONLY when `Recent Status` and/or `Recent Status (Extended)` are selected in the table column view.
We have 8000 candidate records and I cannot figure out why this query is slow. Any guidance would be very much appreciated!
SLOW QUERY:
SELECT SQL_CALC_FOUND_ROWS
candidate.candidate_id AS candidateID,
candidate.candidate_id AS exportID,
candidate.is_hot AS isHot,
candidate.date_modified AS dateModifiedSort,
candidate.date_created AS dateCreatedSort,
IF(candidate_joborder_submitted.candidate_joborder_id, 1, 0) AS submitted,
IF(attachment_id, 1, 0) AS attachmentPresent,
candidate.first_name AS firstName,
candidate.last_name AS lastName,
candidate.city AS city,
candidate.state AS state,
candidate.key_skills AS keySkills,
owner_user.first_name AS ownerFirstName,owner_user.last_name AS ownerLastName,CONCAT(owner_user.last_name, owner_user.first_name) AS ownerSort,
DATE_FORMAT(candidate.date_created, '%m-%d-%y') AS dateCreated,
DATE_FORMAT(candidate.date_modified, '%m-%d-%y') AS dateModified,
(
SELECT
CONCAT(
'<a href="index.php?m=joborders&a=show&jobOrderID=',
joborder.joborder_id,
'" title="',
joborder.title,
' (',
company.name,
')">',
candidate_joborder_status.short_description,
'</a>'
)
FROM
candidate_joborder
LEFT JOIN candidate_joborder_status
ON candidate_joborder_status.candidate_joborder_status_id = candidate_joborder.status
LEFT JOIN joborder
ON joborder.joborder_id = candidate_joborder.joborder_id
LEFT JOIN company
ON joborder.company_id = company.company_id
WHERE
candidate_joborder.candidate_id = candidate.candidate_id
ORDER BY
candidate_joborder.date_modified DESC
LIMIT 1
) AS lastStatus
,
(
SELECT
CONCAT(
candidate_joborder_status.short_description,
'<br />',
'<a href="index.php?m=companies&a=show&companyID=',
company.company_id,
'">',
company.name,
'</a> - ',
'<a href="index.php?m=joborders&a=show&jobOrderID=',
joborder.joborder_id,
'">',
joborder.title,
'</a>'
)
FROM
candidate_joborder
LEFT JOIN candidate_joborder_status
ON candidate_joborder_status.candidate_joborder_status_id = candidate_joborder.status
LEFT JOIN joborder
ON joborder.joborder_id = candidate_joborder.joborder_id
LEFT JOIN company
ON joborder.company_id = company.company_id
WHERE
candidate_joborder.candidate_id = candidate.candidate_id
ORDER BY
candidate_joborder.date_modified DESC
LIMIT 1
) AS lastStatusLong
FROM
candidate
LEFT JOIN attachment
ON candidate.candidate_id = attachment.data_item_id
AND attachment.data_item_type = 100
LEFT JOIN candidate_joborder AS candidate_joborder_submitted
ON candidate_joborder_submitted.candidate_id = candidate.candidate_id
AND candidate_joborder_submitted.status >= 400
AND candidate_joborder_submitted.site_id = 1
AND candidate_joborder_submitted.status != 650
LEFT JOIN user AS owner_user ON candidate.owner = owner_user.user_id LEFT JOIN saved_list_entry
ON saved_list_entry.data_item_type = 100
AND saved_list_entry.data_item_id = candidate.candidate_id
AND saved_list_entry.site_id = 1
WHERE
candidate.site_id = 1
GROUP BY candidate.candidate_id
ORDER BY dateModifiedSort DESC
LIMIT 0, 15;
--------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAIN (above query)
'1', 'PRIMARY', 'attachment', 'system', 'IDX_type_id,IDX_data_item_id,dataitem1', NULL, NULL, NULL, '0', 'const row not found'
'1', 'PRIMARY', 'candidate', 'ALL', 'IDX_site_first_last_modified,IDX_site_id_email_1_2', NULL, NULL, NULL, '8616', 'Using where; Using temporary; Using filesort'
'1', 'PRIMARY', 'candidate_joborder_submitted', 'ref', 'IDX_candidate_id,IDX_site_id,IDX_status_special,IDX_site_joborder', 'IDX_candidate_id', '4', 'opencats.candidate.candidate_id', '1', 'Using where'
'1', 'PRIMARY', 'owner_user', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.candidate.owner', '1', 'Using where'
'1', 'PRIMARY', 'saved_list_entry', 'ref', 'IDX_type_id,IDX_data_item_type,IDX_data_item_id', 'IDX_type_id', '8', 'const,opencats.candidate.candidate_id', '1', 'Using where'
'3', 'DEPENDENT SUBQUERY', 'candidate_joborder', 'index', 'IDX_candidate_id', 'IDX_date_modified', '8', NULL, '1', 'Using where'
'3', 'DEPENDENT SUBQUERY', 'candidate_joborder_status', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.candidate_joborder.status', '1', ''
'3', 'DEPENDENT SUBQUERY', 'joborder', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.candidate_joborder.joborder_id', '1', ''
'3', 'DEPENDENT SUBQUERY', 'company', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.joborder.company_id', '1', 'Using where'
'2', 'DEPENDENT SUBQUERY', 'candidate_joborder', 'index', 'IDX_candidate_id', 'IDX_date_modified', '8', NULL, '1', 'Using where'
'2', 'DEPENDENT SUBQUERY', 'candidate_joborder_status', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.candidate_joborder.status', '1', ''
'2', 'DEPENDENT SUBQUERY', 'joborder', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.candidate_joborder.joborder_id', '1', ''
'2', 'DEPENDENT SUBQUERY', 'company', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.joborder.company_id', '1', 'Using where'
Performed so far (some stabbing in the dark):
create index list on candidate(candidate_id, site_id, is_admin_hidden, date_modified)
create index list1 on candidate_joborder(candidate_id,candidate_joborder_id,status,site_id,date_mofified)
create index dataitem1 on attachment(data_item_id,data_item_type)
--added to IDX_short_description on candidate_jobordrer_status_type with candidate_status_type_id
--take note that candidate_jobordrer_status_type is spelled funky on work jobordrer
create index extra1 on extra_field(data_item_id,field_name,data_item_type)
create index status1 on candidate_joborder_status(candidate_joborder_status_id,short_description)
create index order1 on joborder(joborder_id,company_id,status,title)
We have 8000 candidate records and I cannot figure out why this query is slow. Any guidance would be very much appreciated!
SLOW QUERY:
SELECT SQL_CALC_FOUND_ROWS
candidate.candidate_id AS candidateID,
candidate.candidate_id AS exportID,
candidate.is_hot AS isHot,
candidate.date_modified AS dateModifiedSort,
candidate.date_created AS dateCreatedSort,
IF(candidate_joborder_submitted.candidate_joborder_id, 1, 0) AS submitted,
IF(attachment_id, 1, 0) AS attachmentPresent,
candidate.first_name AS firstName,
candidate.last_name AS lastName,
candidate.city AS city,
candidate.state AS state,
candidate.key_skills AS keySkills,
owner_user.first_name AS ownerFirstName,owner_user.last_name AS ownerLastName,CONCAT(owner_user.last_name, owner_user.first_name) AS ownerSort,
DATE_FORMAT(candidate.date_created, '%m-%d-%y') AS dateCreated,
DATE_FORMAT(candidate.date_modified, '%m-%d-%y') AS dateModified,
(
SELECT
CONCAT(
'<a href="index.php?m=joborders&a=show&jobOrderID=',
joborder.joborder_id,
'" title="',
joborder.title,
' (',
company.name,
')">',
candidate_joborder_status.short_description,
'</a>'
)
FROM
candidate_joborder
LEFT JOIN candidate_joborder_status
ON candidate_joborder_status.candidate_joborder_status_id = candidate_joborder.status
LEFT JOIN joborder
ON joborder.joborder_id = candidate_joborder.joborder_id
LEFT JOIN company
ON joborder.company_id = company.company_id
WHERE
candidate_joborder.candidate_id = candidate.candidate_id
ORDER BY
candidate_joborder.date_modified DESC
LIMIT 1
) AS lastStatus
,
(
SELECT
CONCAT(
candidate_joborder_status.short_description,
'<br />',
'<a href="index.php?m=companies&a=show&companyID=',
company.company_id,
'">',
company.name,
'</a> - ',
'<a href="index.php?m=joborders&a=show&jobOrderID=',
joborder.joborder_id,
'">',
joborder.title,
'</a>'
)
FROM
candidate_joborder
LEFT JOIN candidate_joborder_status
ON candidate_joborder_status.candidate_joborder_status_id = candidate_joborder.status
LEFT JOIN joborder
ON joborder.joborder_id = candidate_joborder.joborder_id
LEFT JOIN company
ON joborder.company_id = company.company_id
WHERE
candidate_joborder.candidate_id = candidate.candidate_id
ORDER BY
candidate_joborder.date_modified DESC
LIMIT 1
) AS lastStatusLong
FROM
candidate
LEFT JOIN attachment
ON candidate.candidate_id = attachment.data_item_id
AND attachment.data_item_type = 100
LEFT JOIN candidate_joborder AS candidate_joborder_submitted
ON candidate_joborder_submitted.candidate_id = candidate.candidate_id
AND candidate_joborder_submitted.status >= 400
AND candidate_joborder_submitted.site_id = 1
AND candidate_joborder_submitted.status != 650
LEFT JOIN user AS owner_user ON candidate.owner = owner_user.user_id LEFT JOIN saved_list_entry
ON saved_list_entry.data_item_type = 100
AND saved_list_entry.data_item_id = candidate.candidate_id
AND saved_list_entry.site_id = 1
WHERE
candidate.site_id = 1
GROUP BY candidate.candidate_id
ORDER BY dateModifiedSort DESC
LIMIT 0, 15;
--------------------------------------------------------------------------------------------------------------------------------------------------
EXPLAIN (above query)
'1', 'PRIMARY', 'attachment', 'system', 'IDX_type_id,IDX_data_item_id,dataitem1', NULL, NULL, NULL, '0', 'const row not found'
'1', 'PRIMARY', 'candidate', 'ALL', 'IDX_site_first_last_modified,IDX_site_id_email_1_2', NULL, NULL, NULL, '8616', 'Using where; Using temporary; Using filesort'
'1', 'PRIMARY', 'candidate_joborder_submitted', 'ref', 'IDX_candidate_id,IDX_site_id,IDX_status_special,IDX_site_joborder', 'IDX_candidate_id', '4', 'opencats.candidate.candidate_id', '1', 'Using where'
'1', 'PRIMARY', 'owner_user', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.candidate.owner', '1', 'Using where'
'1', 'PRIMARY', 'saved_list_entry', 'ref', 'IDX_type_id,IDX_data_item_type,IDX_data_item_id', 'IDX_type_id', '8', 'const,opencats.candidate.candidate_id', '1', 'Using where'
'3', 'DEPENDENT SUBQUERY', 'candidate_joborder', 'index', 'IDX_candidate_id', 'IDX_date_modified', '8', NULL, '1', 'Using where'
'3', 'DEPENDENT SUBQUERY', 'candidate_joborder_status', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.candidate_joborder.status', '1', ''
'3', 'DEPENDENT SUBQUERY', 'joborder', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.candidate_joborder.joborder_id', '1', ''
'3', 'DEPENDENT SUBQUERY', 'company', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.joborder.company_id', '1', 'Using where'
'2', 'DEPENDENT SUBQUERY', 'candidate_joborder', 'index', 'IDX_candidate_id', 'IDX_date_modified', '8', NULL, '1', 'Using where'
'2', 'DEPENDENT SUBQUERY', 'candidate_joborder_status', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.candidate_joborder.status', '1', ''
'2', 'DEPENDENT SUBQUERY', 'joborder', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.candidate_joborder.joborder_id', '1', ''
'2', 'DEPENDENT SUBQUERY', 'company', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'opencats.joborder.company_id', '1', 'Using where'
Performed so far (some stabbing in the dark):
create index list on candidate(candidate_id, site_id, is_admin_hidden, date_modified)
create index list1 on candidate_joborder(candidate_id,candidate_joborder_id,status,site_id,date_mofified)
create index dataitem1 on attachment(data_item_id,data_item_type)
--added to IDX_short_description on candidate_jobordrer_status_type with candidate_status_type_id
--take note that candidate_jobordrer_status_type is spelled funky on work jobordrer
create index extra1 on extra_field(data_item_id,field_name,data_item_type)
create index status1 on candidate_joborder_status(candidate_joborder_status_id,short_description)
create index order1 on joborder(joborder_id,company_id,status,title)