MySQL query is taking a long time

Question

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.

Answer 1

The following index may provide some improvement.

ALTER TABLE ha_manage_activity
ADD INDEX dba246341wr (companyID, activityID, dateCreated)

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 the SELECT 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 the WHERE 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.

Answer 2

ha_manage_activity: INDEX(companyID, projectID)
ha_manage_activity: INDEX(companyID, activityID)

(It is unclear which the Optimizer would prefer.)

And, break out ha_manage_activity.projectID > 0 since it is common to the 3 ORs:

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.taskID > 0 AND ha_manage_activity.subtaskID = 0 AND taskName IS NOT NULL) OR   
(ha_manage_activity.taskID > 0 AND ha_manage_activity.subtaskID > 0 AND subtaskName IS NOT NULL)
                                         )  

This gives the Optimizer a better chance of using projectID from the index.