How optimize MySQL query: users read last message

Question

please help optimize query.

I has table with fields

'comment_parent' for users who receive message,
'user_id' for user who send message,
comment_karma set to 1 when user read message
'updated' is datetime when user read message

How get list of favorites, who read last message on last 7 days.

Are has best way for found last message then SELECT max(updated) ?

    SELECT wc.comment_parent,wc.user_id, wc.updated FROM `wp_comments` wc
     WHERE (wc.comment_parent IN (4786,322,1492,257,4760,40,41) AND wc.user_id=1)
     AND wc.`comment_karma` = 1
     AND updated = (SELECT max(`updated`) FROM `wp_comments` as cc WHERE cc.`user_id`= wc.user_id AND comment_parent = wc.comment_parent)
     AND updated > DATE_SUB(NOW(),INTERVAL 1 WEEK)
     ORDER BY wc.updated DESC LIMIT 0 , 30

What is the issue with this version? – Gordon Linoff Jul 16 '17 at 16:02 — Gordon Linoff, Jul 16 '17 at 16:02
No issue, but maybe has best way? – Sergey Panasenko Jul 16 '17 at 16:11 — Sergey Panasenko, Jul 16 '17 at 16:11

score 2 · Accepted Answer · answered Jul 16 '17 at 17:01

Your query looks fine to me, though there other ways to retrieve the same result. However - If you just want to select these columns (comment_parent, user_id, updated), you can just use this query:

SELECT wc.comment_parent, wc.user_id, max(wc.updated) as updated
FROM wp_comments wc
WHERE wc.comment_parent IN (4786,322,1492,257,4760,40,41)
  AND wc.user_id=1
  AND wc.updated > NOW() - INTERVAL 1 WEEK
  AND wc.`comment_karma` = 1
GROUP BY wc.user_id, wc.comment_parent
ORDER BY max(wc.updated) desc

Thank a lot, you variant faster! – Sergey Panasenko Jul 17 '17 at 08:43 — Sergey Panasenko, Jul 17 '17 at 08:43

score 1 · Answer 2 · answered Jul 16 '17 at 16:29

You can optimize a bit your query, and benefit for a specific index:

SELECT 
    wc.comment_parent,wc.user_id, wc.updated 
FROM 
    `wp_comments` wc
WHERE 
        wc.comment_parent IN (4786,322,1492,257,4760,40,41) 
    AND wc.user_id = 1
    AND wc.comment_karma = 1
    AND updated = 
       (SELECT 
            max(`updated`) 
       FROM 
           wp_comments as cc 
       WHERE 
               /* put here the updated restriction, 
                  the subquery will take care of everything */
               cc.updated > now() - INTERVAL 1 WEEK
           AND cc.user_id = wc.user_id 
           AND cc.comment_parent = wc.comment_parent
       )
ORDER BY 
    wc.updated DESC 
LIMIT 
    0 , 30 ;

And create the following index:

CREATE INDEX idx_wp_comment_rel 
    ON wp_comments (user_id, comment_parent, updated DESC, comment_karma) ;

The index is relatively small, yet it will allow the subquery to just check the index (all the relevant data for it to check is there). Note that current versions of MySQL and MariaDB recognize the DESC in the index, but do not honor it. The day the do, finding the max(update) is straightforward.

You can check the setup and see the execution planst at dbfiddle here

Thanks for you answer and new tool (dbfiddle). Next question, why index with some fields is better then with one? CREATE INDEX idx_wp_comment_rel ON wp_comments (user_id, comment_parent, updated DESC, comment_karma) ; CREATE INDEX idx_wp_comment_up ON wp_comments ( updated DESC) ; — Sergey Panasenko, Jul 17 '17 at 08:48
This is a [*covering index*](https://stackoverflow.com/questions/62137/what-is-a-covered-index): all the information in your query is *already* in your index; so, the database engine doesn't need to look at the *actual table* to perform the subquery. Also, by carefully choosing the order of the columns in the index, you make the most likely needed ones to be together, and reduce the amount of data pages the DBMS needs to read. — joanolo, Jul 17 '17 at 11:16

How optimize MySQL query: users read last message

2 Answers2