Is there a simpler way to achieve this style of user messaging?

hmm maybe i'm not understanding correctly your problem... but to me the solution is quite simple:

SELECT c.*, MAX(m.time) as latest_post FROM conversations as c INNER JOIN messages as m ON c.id = m.conversation_idWHERE c.userId = 222 OR c.friendId = 222 GROUP BY c.idORDER BY latest_post DESC

here's my test data:

Conversations :

id  userId  friendId1   222     3332   222     444

Messages :

id  message     time (Desc)     conversation_id14  rty     2012-05-14 19:59:55     213  cvb     2012-05-14 19:59:51     112  dfg     2012-05-14 19:59:46     211  ert     2012-05-14 19:59:42     11   foo     2012-05-14 19:22:57     22   bar     2012-05-14 19:22:57     23   foo     2012-05-14 19:14:13     18   wer     2012-05-13 19:59:37     29   sdf     2012-05-13 19:59:24     110  xcv     2012-05-11 19:59:32     24   bar     2012-05-10 19:58:06     16   zxc     2012-05-08 19:59:17     25   asd     2012-05-08 19:58:56     17   qwe     2012-05-04 19:59:20     1

Query result :

id  userId  friendId    latest_post2   222     444     2012-05-14 19:59:551   222     333     2012-05-14 19:59:51

If that's not it... just ignore my answer :P

Hope this helps

If you're asking for a way to be able to keep all of your current functionality and work flows, yet keep the data in a single table I think you're pretty close.

Instead of having the conversationId be a key to a different table, I would instead have it point to the ID of the message that began the conversation. This would create a parent-child relationship between messages that began a conversation and all those that followed after it. To be able to see all conversations, you would just select all messages where the conversationId is null. Below is a representation of a 2 message conversation:

+----+---------+------+------------------+----------------+--------+----------+| id | message | read | time             | conversationId | toUser | fromUser |+----+---------+------+------------------+----------------+--------+----------+| 1  |  test 1 |  0   | (some timestamp) |  null          |  3     |   4      || 2  |  test 2 |  0   | (some timestamp) |   1            |  4     |   3      |+----+---------+------+------------------+----------------+--------+----------+

The conversation was initiated by user 3. All messages in the conversation can be filter by conversationId. One limitation of this design is that only 2 users can be apart of the conversation.

Update

You could get the last message given a conversation id this way:

SELECT id, message FROM userMessages WHERE conversationId = {conversationId} ORDER BY time DESC LIMIT 1

If you can only have one conversation between users, I don't see a reason for a dedicated conversations table. For this query to work fast, you would need a composite index on (user, message_id) which is impossible if these fields are in different tables. Move user_id and friend_id to the userconversations. This will make your table 8 bytes per record heavier (even assuming 8-byte identifiers) which is hardly a problem for a table containing text messages.

If you have few conversations per user with many messages in each, use this:

SELECT  um.*FROM    (        (        SELECT  MAX(id) AS messageId        FROM    usermessages m1        WHERE   user_id = :me        GROUP BY                friend_id        ORDER BY                messageId DESC        LIMIT 10        )        UNION ALL        (        SELECT  MAX(id) AS messageId        FROM    usermessages m1        WHERE   frient_id = :me        GROUP BY                user_id        ORDER BY                messageId DESC        LIMIT 10        )        ) qJOIN    usermessages umON      um.id = q.messageIdORDER BY        id DESCLIMIT 10

Create separate indexes on user_id and friend_id

If you have many conversations with few messages in each, use this query:

(SELECT  *FROM    usermessages umWHERE   user_id = :me        AND id =         (        SELECT  MAX(id)        FROM    usermessages umi        WHERE   umi.user_id = um.user_id                AND umi.friend_id = um.friend_id        )ORDER BY        id DESCLIMIT 10)UNION ALL(SELECT  *FROM    usermessages umWHERE   frient_id = :me        AND id =         (        SELECT  MAX(id)        FROM    usermessages umi        WHERE   umi.user_id = um.user_id                AND umi.friend_id = um.friend_id        )ORDER BY        id DESCLIMIT 10)ORDER BY        id DESCLIMIT 10

The idea behind this query is that it just descends all messages for the given user, checking that each message is the last in its conversation. This may be much faster than sorting all last messages for all conversations (if you have many of them).

For this to work fast, create indexes on

friend_iduser_id, friend_id