MySQL - How can I interpret EXPLAIN results and optimize this query?

Question

Looking for an understanding of what my EXPLAIN results mean, and optimizing this query and my tables as best as possible.

Request:

 SELECT i.pending, i.itemid, i.message, i.cid, i.dateadded, i.entrypoint, SUM(CASE WHEN v.direction = 1 THEN 1 WHEN v.direction = 2 THEN -1 ELSE 0 END) AS votes, c.name AS cname, c.tag AS ctag, i.userid, (SELECT COUNT(commentid) FROM `comments` WHERE comments.itemid = i.itemid) AS commentcount, CASE WHEN NOT EXISTS (SELECT voteid FROM `votes` WHERE votes.itemid = i.itemid AND votes.userid = @userid) THEN '0' ELSE '1' END AS hasVoted, CASE WHEN NOT EXISTS (SELECT voteid FROM `user_favorites` WHERE user_favorites.itemid = i.itemid AND user_favorites.userid = @userid) THEN '0' ELSE '1' END AS isFavorite FROM `contentitems` i LEFT JOIN votes v ON i.itemid = v.itemid LEFT JOIN `user_favorites` uv ON i.itemid = uv.itemid AND (uv.userid = @userid) INNER JOIN `categories` c ON i.cid = c.cid GROUP BY i.itemid HAVING SUM(CASE WHEN v.direction = 1 THEN 1 WHEN v.direction = 2 THEN -1 ELSE 0 END) > -3 AND i.pending = 0 ORDER BY i.dateadded DESC 

(editable formatting)

Explanation Results:

 +----+--------------------+----------------+--------+-------------------------+-------------------------+---------+------------------------+------+------------------------------------------------------- | id | select_type | table | type | possible_keys key | key_len | ref | rows | Extra | +----+--------------------+----------------+--------+-------------------------+-------------------------+---------+------------------------+------+------------------------------------------------------+ | 1 | PRIMARY | i | ALL | NULL | NULL | NULL | NULL | 121 | Using temporary; Using filesort | | 1 | PRIMARY | v | ref | fk_contentitemsitemid_votesitemid | fk_contentitemsitemid_votesitemid | 4 | db33481_mydb.i.itemid | 2 | | | 1 | PRIMARY | uv | ALL | NULL | NULL | NULL | NULL | 7 | | | 1 | PRIMARY | c | eq_ref | PRIMARY | PRIMARY | 4 | db33481_mydb.i.cid | 1 | | | 4 | DEPENDENT SUBQUERY | user_favorites | ALL | NULL | NULL | NULL | NULL | 7 | Using where | | 3 | DEPENDENT SUBQUERY | votes | ref | fk_contentitemsitemid_votesitemid | fk_contentitemsitemid_votesitemid | 4 | func | 2 | Using where | | 2 | DEPENDENT SUBQUERY | comments | ALL | NULL | NULL | NULL | NULL | 26 | Using where | +----+--------------------+----------------+--------+-------------------------+-------------------------+---------+------------------------+------+------------------------------------------------------+ 

Answer 1

Firstly, you have an id of no voice, and then make a left join in the from list and, finally, the amount in stock. It clogs your voice three times. If each vote is possibly associated with one “ItemID”, then it would be best to pre-aggregate on its own, since its own “amount” was made ONCE.

In addition, since your final “HAVING” sentence is the direct basis of the votes, having a left voice connection becomes dead and eventually ends with a regular JOIN.

All that was said, I would first request FIRST for those votes that FINISH with the qualification condition HAVING in front, then join the content elements and other associations ... The request with User_Favorites is a counter and will be either 0 (not found) or 1 ( found). Should not be necessary in case / when

My first query alias "PQ" represents "PreQuery"

 SELECT PQ.ItemID, PQ.VSum as Votes, PQ.HasVoted, i.pending, i.itemid, i.message, i.cid, i.dateadded, i.entrypoint, i.userid, c.name AS cname, c.tag AS ctag, ( SELECT COUNT(commentid) FROM `comments` WHERE comments.itemid = PQ.itemid) AS commentcount, ( SELECT COUNT(*) FROM user_favorites uf WHERE uf.itemid = PQ.itemid AND uf.userid = @userid ) AS isFavorite from ( SELECT v.itemid, SUM( case when v.Direction = 1 then 1 when v.Direction = 2 then -1 ELSE 0 end ) as VSum, MAX( if( votes.userid = @userid, 1, 0 ) AS HasVoted from votes v group by v.itemid having VSum > -3 ) PQ JOIN ContentItems i ON PQ.ItemID = i.ItemID and i.Pending = 0 JOIN Categories c ON i.cid = c.cid ORDER BY i.dateadded DESC 

Others pointed to the need for indices agreed upon. I would make sure that each table has a corresponding index on the user ID or product identifier (or where necessary).

A couple of other points ... Initially, you run a query requesting all ContentItems, but leaving a vote ... But then applying the user ID element. It DEFINITELY smells like a request for a specific user. At the same time, I MUST pre-run the entire request by selecting only the ItemID with which the user identifier did something with ... THEN continue the request.

Answer 2

I see that there is no key used to access comments , votes and user_favorites . If the tables are really small, try adding an index to userid and itemid to these tables.

Answer 3

Try to see the link to understand the plan of explanation. Try to go down this section, it clearly explains what you need to look for.

Moreover, your explanation plan looks like less information. try using sql developer from oracle. it is open source and gives you details of a plan of explanation.

Answer 4

I would add the following indexes:

ALTER TABLE comments ADD INDEX (commentid)
ALTER TABLE user_favorites ADD INDEX (itemid, voteid)

Also, if the possible_keys column says NULL, it means that there are no useful keys for this table. Even if they are not used for optimization, they will appear there if they exist for the column in the query. Most likely, you have a primary key in these tables in a column that is not accessed in the query.