MySQL Query causing high CPU and taking forever to execute

Question

this below query takes a long time to execute in excess of 120 seconds, upon checking the explain plan it seems to be using using where, using temporary; using filesort. Since the application is a third party I do

SELECT ii.*, 
count(iic.postDate) as itemCommentCount, 
count(iif.postDate) as itemFileCount,
iis.statusDate as itemStatus_statusDate, 
iis.statusKey as itemStatus_statusKey, 
pp.name as project_name, 
mm.title as member_title, 
mm.firstName as member_firstName, 
mm.middleName as member_middleName, 
mm.lastName as member_lastName, 
mm.username as member_username, 
mp.position 
FROM 
    frk_item as ii INNER JOIN frk_itemStatus AS iis ON ii.itemId = iis.itemId 
    LEFT JOIN frk_project AS pp ON ii.projectId = pp.projectId 
    LEFT JOIN frk_memberProject AS mp ON ii.projectId = mp.projectId AND mp.memberId=5                 LEFT JOIN frk_member AS mm ON ii.memberId = mm.memberId 
    LEFT JOIN frk_itemComment AS iic ON ii.itemId=iic.itemId 
    LEFT JOIN frk_itemFile AS iif ON ii.itemId=iif.itemId  
WHERE 
    ii.projectId = '2' AND (showPrivate=0 OR showPrivate=1 OR (showPrivate=2 AND (ii.memberId=5 OR ii.authorId=5)))
     AND ((deadlineDate < '2015-09-15' OR statusKey = 3)) 
     AND iis.statusDate=(
        SELECT MAX(iis2.statusDate) 
            FROM frk_itemStatus AS iis2 
            WHERE 
                ii.itemId = iis2.itemId
         ) 
     AND iis.statusDate=(
        SELECT  MAX(iis2.statusDate) 
            FROM frk_itemStatus AS iis2 
                WHERE ii.itemId = iis2.itemId
            ) 
GROUP BY ii.itemId , ii.itemId ORDER BY deadlineDate ASC, deadlineDate ASC, priority 

I feel the query is poorly designed however as its a part of 3rd party software, dont have much control of what can be done. Is there something on the index front I can do to speed it up?

the indexes on the frk_item look like

 SHOW INDEX FROM frk_item;

| Table    | Non_unique | Key_name                   | Seq_in_index | Column_name    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| frk_item |          0 | PRIMARY                    |            1 | itemId         | A         |       37237 |     NULL | NULL   |      | BTREE      |         |               |
| frk_item |          1 | projectId                  |            1 | projectId      | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| frk_item |          1 | memberId                   |            1 | memberId       | A         |          14 |     NULL | NULL   |      | BTREE      |         |               |
| frk_item |          1 | missing_authoriid          |            1 | authorId       | A         |          13 |     NULL | NULL   |      | BTREE      |         |               |
| frk_item |          1 | missing_showprivate        |            1 | showPrivate    | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
| frk_item |          1 | frk_item_fulltext          |            1 | title          | NULL      |       37237 |     NULL | NULL   |      | FULLTEXT   |         |               |
| frk_item |          1 | frk_item_fulltext          |            2 | description    | NULL      |       37237 |     NULL | NULL   |      | FULLTEXT   |         |               |
| frk_item |          1 | frk_item_fulltext          |            3 | cannedQuestion | NULL      |       37237 |     NULL | NULL   |      | FULLTEXT   |         |          |
+----------+------------+----------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

and

##EXPLAIN PLAN for the query ##

| id | select_type        | table | partitions | type   | possible_keys                                                                                    | key       | key_len | ref                                | rows  | filtered | Extra                                              |
+----+--------------------+-------+------------+--------+--------------------------------------------------------------------------------------------------+-----------+---------+------------------------------------+-------+----------+----------------------------------------------------+
| 1 | PRIMARY            | ii    | NULL       | ref    | PRIMARY,projectId,memberId,missing_authoriid,missing_showprivate,frk_item_fulltext               | projectId | 3       | const                              | 18618 |   50.00 | Using where; Using temporary; Using filesort       |
| 1 | PRIMARY            | pp    | NULL       | eq_ref | PRIMARY,pranav_project                                                                           | PRIMARY   | 3       | library_support.ii.projectId       |    1   100.00 | NULL                                               |
| 1 | PRIMARY            | mp    | NULL       | eq_ref | PRIMARY                                                                                          | PRIMARY   | 6       | const,library_support.ii.projectId |    1 |  100.00 | NULL                                               |
| 1 | PRIMARY            | mm    | NULL       | eq_ref | PRIMARY                                                                                          | PRIMARY   | 3       | library_support.ii.memberId        |    1 |  100.00 | NULL                                               |
| 1 | PRIMARY            | iic   | NULL       | ref    | taskId                                                                                           | taskId    | 4       | library_support.ii.itemId          |    1 |  100.00 | NULL                                               |
| 1 | PRIMARY            | iif   | NULL       | ALL    | taskId                                                                                           | NULL      | NULL    | NULL                               |    1 |  100.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | PRIMARY            | iis   | NULL       | ref    | itemId                                                                                           | itemId    | 4       | library_support.ii.itemId          |    3 |  100.00 | Using where                                        |
| 3 | DEPENDENT SUBQUERY | iis2  | NULL       | ref    | itemId                                                                                           | itemId    | 4       | library_support.ii.itemId          |    3 |  100.00 | NULL                                               |
| 2 | DEPENDENT SUBQUERY | iis2  | NULL       | ref    | itemId                                                                                           | itemId    | 4       | library_support.ii.itemId          |    3 |  100.00 | NULL                                               |
+----+--------------------+-------+------------+--------+--------------------------------------------------------------------------------------------------+-----------+---------+------------------------------------+-------+----------+----------------------------------------------------+

MySQL engine being used is InnoDB and version is 5.7.8rc

Answer 1

The two dependent subqueries seem to be identical?? Remove one.

GROUP BY ii.itemId , ii.itemId Eh?? Remove one of them. deadlineDate ASC, deadlineDate ASC Ditto.

Index on frk_itemStatus: INDEX(itemId, statusDate)

AND ((deadlineDate < '2015-09-15' OR statusKey = 3))

OR can be deadly on performance. Consider using UNION:

( SELECT ... AND deadlineDate < '2015-09-15' ... )
UNION DISTINCT
( SELECT ... AND statusKey = 3 ... )

together with

INDEX(projectId, deadlineDate)
INDEX(projectId, statusKey)

Ah. Perhaps the best idea came when I noticed GROUP BY ii.item_id. Grouping by a unique key is a symptom of exploding number of rows due to JOINs, then reigning them back in by using that grouping. So, instead,...

First, formulate this to get just the ids of the interesting rows.

( SELECT itemId FROM frk_item ... )

Then, do the JOINs to get the rest of the data:

SELECT lots-of-stuff
    FROM ( SELECT itemId FROM frk_item ... ) AS ids
    JOIN frk_item AS f  ON f.itemId = ids.itemId  -- to get other frk_item fields
    JOIN the-rest-of-the-joins
    WHERE ...   -- if not already moved into the subquery
    # GROUP BY  -- remove!
    ORDER BY ...

This speeds things up by (1) avoiding the explode-implode and (2) avoiding the tmp table for the GROUP BY.