Please describe the issue you're having
If you accept the answer, please mark the topic as [SOLVED] by clicking the tick.

Moderators: RussH, cptr13

Forum rules: Just please remember to play nicely once you walk through the door. You can disagree with us, or any other commenters in this forum, but respect our space and keep your comments directed to the topic at hand.
#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)
#3526
From what I can tell this section is what is the part that is slow however I am not sure what direction i should go in fix it.

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

It's essential to keep these synchronized to ensur[…]

Export part in Job Order order search

Same problem as mine. Maybe you're right, I'll che[…]

The error message you're encountering indicates th[…]

Errors restoring mysqldump backup

doesnot work for me