optmize a select query (if necessary creating index)-CodePudding

I have 2 tables:

chats (id, ..., chat_status_id) // (about 28k records)
chat_messages(id, chat_id, send_date, ...) // (about 1 million records)

I need to get chats of certain status with latest message.

This is the select I am using, but it's pretty slow (it works in the end):

SELECT c.*,
       p1.*
FROM   chats c
       JOIN chat_messages p1
         ON ( c.id = p1.chat_id )
       LEFT OUTER JOIN chat_messages p2
                    ON ( c.id = p2.chat_id
                         AND ( p1.send_date < p2.send_date
                                OR ( p1.send_date = p2.send_date
                                     AND p1.id < p2.id ) ) )
WHERE  p2.id IS NULL
AND c.chat_status_id = 1
ORDER  BY p1.send_date DESC

I do not know howto optimize it.

CodePudding user response：

I would start with a few index updates. First, your WHERE clause is based on the CHATS table status, that should be first in your index order, but can have the ID as well AFTER to support the joins. Next, your messages table. Similarly, those are JOINed based on the CHAT ID, not its own unique table ID as the primary consideration, but does use the ID as the carry-along for conditional testing of p1.id < p2.id. Suggestions as follows.

Table          Index
Chats          ( Chat_Status_Id, id )
Chat_Messages  ( Chat_id, id, send_date )

CodePudding user response：

Give this a try:

SELECT  c.*, p1.*
    FROM  chats c
    JOIN  chat_messages p1  ON ( c.id = p1.chat_id )
    WHERE  NOT EXISTS 
    (
        SELECT  1
            FROM  chat_messages p2
            WHERE  c.id = p2.chat_id
              AND  ( p1.send_date < p2.send_date
                      OR  ( p1.send_date = p2.send_date
                              AND  p1.id < p2.id ) ) 
    )
    WHERE  c.chat_status_id = 1
    ORDER BY  p1.send_date DESC

With

chats:  INDEX(chat_status_id, id)
chat_messages:  INDEX(chat_id, send_date,  id)