Max Max - 1 month ago 6
MySQL Question

replace subquery mySQL with Join

I am building a web-based chatting system (mysql, php, jquery), listed by conversations, where each conversation shows the last message sent only. (like fb).

the table has the next fields, where

conversation_code
is a unique token, shared by all the messages whitin the conversation.

id_message, id_sender, id_receiver, conversation_code, message_date,


I am currently working with this SQL query:

SELECT * FROM table_messages
WHERE message_date IN (SELECT max(message_date)
FROM table_messages WHERE id_sender='$id_session' OR id_receiver='$id_session'
GROUP BY conversation_code)
ORDER BY message_date DESC


This query,returns results as expected but delays too much to load (ajax). I was told that this delay is because the using of subqueries. Is there any other way to replace the subquery for
JOIN
or something else in order to avoid the delay?

Answer

This is based on an inner join (dinamic) instead of in

SELECT a.* FROM table_messages as a
INNER JOIN 
     (SELECT max(message_date) as md
          FROM table_messages 
          WHERE  id_sender='$id_session' OR  id_receiver='$id_session'   
          GROUP BY  conversation_code) t on t.md = a.message_date  
 ORDER BY a.message_date DESC 
Comments