I have a mysql table containing messages:

id (INT)
sender_id (INT)
recipient_id (INT)
sent_time (INT)
read_time (INT)
body (TEXT)

I need to retrieve a list of most recent messages user X received from or sent to other (distinct) users.

In other words I'd like to create a full list of conversations with other users, sorted by sent_time of last message exchanged with a given user. The end result (processed in PHP) is going to like like this:

array (
    array (other_user_1_id, last_msg_sent_time, last_msg_is_read,
           last_msg_is_to_me/from_me, number_of_messages*),
    array (other_user_2_id, ... )

*) number_of_messages is optional, but rather nice to have.

Question: How do I do this most efficiently? Create a separate table "conversations" and update it every time anyone sends or reads a message or create a single, but sophisticated query on "messages" table? If the latter, what would the query look like?


Something along these lines:

$id = 12345 // Id for the user you are pulling messages for
$messages = Array();

$result = mysql_query("SELECT sender_id, recepient_id, sent_time, read_time FROM messages WHERE sender_id = $id OR recepient_id = $id");

while ($row = mysql_fect_assoc($result)) {

  $directon = ( $row['recepient_id'] == $id ) ? 'to' : 'from';
  $isRead = ( (bool)$row['read_time'] );
  $messages[] = Array( $direction, $isRead .......... )


for number of messages in a convo, you will have to make a new table for conversations, or work out a logic governing what makes a set of messages fall into a specific conversation.

Yeah, but that means I'd have to retrieve all the messages of a given user every time he takes a look at his mailbox, which I'd like to avoid if possible to give the server a break. I was trying to create a query with UNION and DISTINCT but got lost along the way. The other way I was thinking about was getting the newest messages in several queries and then combining the arrays in php, but then I'd have to sort a multidimensional array afterwards and I'd still be retrieving something like two times as many rows as needed. :/

I would take the advice of the code given in codeburger's post but to add to that you have other options (as it sounds like his code is not anything new in regards to a solution).

Possible options include:

  1. indexing on both the sender_id (INT) and recipient_id (INT) to increase search time

  2. creating a separate table for each sender_id (that way when you know who the conversation is between - assuming it is only a 1 on 1 conversation - you can just pull from two tables where the ids are common)

3, my favorite idea, create an additional column that references a transaction database and keep a history of each transaction, indexed off the conversation number... it's probably the "worst" in some ways, but it's more my organizational style.

if you have any questions, i can elaborate, in fact, i'll probably update this later once i get home

Or a variation on the third option - create additional column containing the id of a first row of a conversation. This way we could do a SELECT * WHERE sender_id = '$uid' OR recipient_id = '$uid' GROUP BY first_id ORDER BY sent_date DESC.Hey, even better - I can add yet another column, containing the sequential number of given message in conversation between these two users. As long as there are no deletes, it should work quite well. :)Any thoughts on that?
just have your deletes update the tables... indexing is CRUCIAL in this though, very very important... i do work with large quantities of transactions and thats all thats helped me
I wouldn't even think about doing that without indexing. Huge, huge difference, I know. :)Thanks a lot!