Database Administrators Asked by pg2286 on January 8, 2021
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
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.
Answered by Rick James on January 8, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP