CODEWITHSUNDEEP
mysql
fajar setiawan
fajar setiawan

Reputation: 77

what is the best way to sort chat room by most recent chat on each room

I have a chat_hist table with structure as follow: 

|chat_id (auto increment)|room_id(int)|sender_id(int)|messege(text)|timestamp|

so what is the best way (performance wise) to list chat_room(room_id) based on most recent chat(timestamp) from each chat_room (just like list on Whatsapp group)?

I am asking this since I am no DB expert and I imagine that the chat message in each room growing what happen the performance when each user refresh the most recent list

what i use for now is this:

select room_id from (
    select room_id,max(time_stamp) as ts from chat_hist where room_id in (
        select distinct room_id from chat_room
    )
    group by room_id
) as A order by ts desc

where table chat_room is where all the room_id reside.

So what is the best way to do this...

Thank you in advance

Upvotes: 0

Views: 137

Answers (1)

O. Jones
O. Jones

Reputation: 108776

Your chat_id autoincrements. So presumably it goes up as timestamp goes up.

Therefore, you can find the latest row in chat_hist for each room_id like this

            SELECT room_id, MAX(chat_id) chat_id
              FROM chat_hist
             GROUP BY room_id

If you have an index on room_id this subquery will be very fast; it uses a so-called loose index scan.

Then, you can use that as a subquery to grab the latest chat detail for each room:

SELECT c.*
  FROM chat_hist c
  JOIN (
            SELECT room_id, MAX(chat_id) chat_id
              FROM chat_hist
             GROUP BY room_id

       ) latest ON c.chat_id = latest.chat_id

Upvotes: 1

Related Questions