I have a performance issue with a MySQL query that takes a long time. I'm trying to figure out why.
MySQL Version is 5.6 and Running on Cloud
The query that works but takes a long time is:
SELECT `ha_manage_activity`.*, `firstName`, `lastName`, `avatar`, `ha_manage_activity_types`.`template`, `ha_manage_projects`.`projectName`, `ha_manage_tasks`.`taskName`,
`ha_manage_subtasks`.`subtaskName`, `ha_manage_projects`.`comments` as project_comment, `ha_manage_tasks`.`comments` as task_comment, `ha_manage_subtasks`.`comments` as subtask_comment,
`ha_manage_projects`.`closed` as project_closed, `ha_manage_tasks`.`completed` as task_completed, `ha_manage_subtasks`.`completed` as subtask_completed,
ROUND(ha_manage_timesheets.seconds/3600, 2) as hours
FROM (`ha_manage_activity`)
JOIN `ha_users` ON `ha_users`.`userID` = `ha_manage_activity`.`userID`
JOIN `ha_manage_projects` ON `ha_manage_projects`.`projectID` = `ha_manage_activity`.`projectID` AND ha_manage_projects.deleted = 0
LEFT JOIN `ha_manage_tasks` ON `ha_manage_tasks`.`taskID` = `ha_manage_activity`.`taskID` AND ha_manage_tasks.deleted = 0
LEFT JOIN `ha_manage_subtasks` ON `ha_manage_subtasks`.`subtaskID` = `ha_manage_activity`.`subtaskID` AND ha_manage_subtasks.deleted = 0 LEFT JOIN `ha_manage_activity_types` ON `ha_manage_activity_types`.`activityTypeID` = `ha_manage_activity`.`activityTypeID`
LEFT JOIN `ha_manage_timesheets` ON `ha_manage_timesheets`.`entryID` = `ha_manage_activity`.`timesheetID`
LEFT JOIN `ha_manage_team_users` ON `ha_manage_projects`.`projectID` = `ha_manage_projects`.`projectID` AND ha_manage_team_users.userID = "40586"
JOIN `ha_manage_shared_projects` ON `ha_manage_shared_projects`.`projectID` = `ha_manage_projects`.`projectID` AND (ha_manage_shared_projects.userID = "40586" OR ha_manage_shared_projects.teamID = ha_manage_team_users.teamID)
LEFT JOIN `ha_manage_shared_tasks` ON `ha_manage_shared_tasks`.`taskID` = `ha_manage_tasks`.`taskID` AND (ha_manage_shared_tasks.userID = "40586" OR ha_manage_shared_tasks.teamID = ha_manage_team_users.teamID)
LEFT JOIN `ha_manage_shared_subtasks` ON `ha_manage_shared_subtasks`.`subtaskID` = `ha_manage_subtasks`.`subtaskID` AND (ha_manage_shared_subtasks.userID = "40586" OR ha_manage_shared_subtasks.teamID = ha_manage_team_users.teamID)
WHERE `ha_manage_activity`.`companyID` = '31852'
AND (
(ha_manage_activity.projectID > 0 AND ha_manage_activity.taskID = 0 AND ha_manage_activity.subtaskID = 0 AND projectName IS NOT NULL) OR
(ha_manage_activity.projectID > 0 AND ha_manage_activity.taskID > 0 AND ha_manage_activity.subtaskID = 0 AND taskName IS NOT NULL) OR
(ha_manage_activity.projectID > 0 AND ha_manage_activity.taskID > 0 AND ha_manage_activity.subtaskID > 0 AND subtaskName IS NOT NULL) )
GROUP BY `ha_manage_activity`.`activityID`
ORDER BY `ha_manage_activity`.`dateCreated`
desc LIMIT 50
This query takes about 1 min 30 seconds averagely and returns 50 rows:
50 rows in set (1 min 30.17 sec)
I have ran EXPLAIN to check what MySQL is doing:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: ha_manage_projects
type: ref
possible_keys: PRIMARY,deleted
key: deleted
key_len: 1
ref: const
rows: 206834
Extra: Using index condition; Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: ha_manage_shared_projects
type: ref
possible_keys: userID,teamID,projectID
key: projectID
key_len: 5
ref: projectbubble.ha_manage_projects.projectID
rows: 1
Extra: NULL
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: ha_manage_activity
type: ref
possible_keys: PRIMARY,idx_ids
key: idx_ids
key_len: 4
ref: projectbubble.ha_manage_projects.projectID
rows: 6
Extra: Using index condition; Using where
*************************** 4. row ***************************
id: 1
select_type: SIMPLE
table: ha_users
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: projectbubble.ha_manage_activity.userID
rows: 1
Extra: NULL
*************************** 5. row ***************************
id: 1
select_type: SIMPLE
table: ha_manage_tasks
type: eq_ref
possible_keys: PRIMARY,deleted,deleted_2
key: PRIMARY
key_len: 4
ref: projectbubble.ha_manage_activity.taskID
rows: 1
Extra: Using where
*************************** 6. row ***************************
id: 1
select_type: SIMPLE
table: ha_manage_subtasks
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: projectbubble.ha_manage_activity.subtaskID
rows: 1
Extra: Using where
*************************** 7. row ***************************
id: 1
select_type: SIMPLE
table: ha_manage_activity_types
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: projectbubble.ha_manage_activity.activityTypeID
rows: 1
Extra: Using where
*************************** 8. row ***************************
id: 1
select_type: SIMPLE
table: ha_manage_timesheets
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: projectbubble.ha_manage_activity.timesheetID
rows: 1
Extra: NULL
*************************** 9. row ***************************
id: 1
select_type: SIMPLE
table: ha_manage_team_users
type: ref
possible_keys: userID
key: userID
key_len: 4
ref: const
rows: 1
Extra: Using where
*************************** 10. row ***************************
id: 1
select_type: SIMPLE
table: ha_manage_shared_tasks
type: ref
possible_keys: userID,teamID,taskID
key: taskID
key_len: 5
ref: projectbubble.ha_manage_tasks.taskID
rows: 1
Extra: Using where
*************************** 11. row ***************************
id: 1
select_type: SIMPLE
table: ha_manage_shared_subtasks
type: ref
possible_keys: userID,taskID
key: taskID
key_len: 5
ref: projectbubble.ha_manage_subtasks.subtaskID
rows: 1
Extra: Using where
11 rows in set (0.02 sec)
Is there something I can optimize to gain performance? Any help is highly appreciated.
Best Answer
The following index may provide some improvement.
Unfortunately, using this index will still require a double lookup, as many other columns of
ha_manage_activity
are still required for filtering the query and joining to other tables, as well as all of them being needed for theSELECT
clause.You could also try adding all the other columns in this table that appear in the other joins to the end of this index. Then add all remaining columns after that, for the
SELECT
clause. That will remove the double lookup issue, but will leave you with basically a duplicate of the table.Even with that your performance may not be that greatly improved, as you have a large number of
OR
statements in both theWHERE
clause and in various joins. Those will wreck performance in most cases.Include the
SHOW CREATE TABLE tablename
statements for each table in your question, and more help may be possible.