Existe uma maneira mais simples de alcançar esse estilo de mensagens do usuário?

Eu criei um sistema de mensagens para os usuários, permite-lhes enviar uma mensagem para outro usuário. Se é a primeira vez que eles falam, então uma nova conversa é iniciada, se não a conversa antiga continua.

A caixa de entrada dos usuários lista todas as conversas que o usuário teve com todos os outros usuários, que são ordenados pela conversa que tem a última postagem.

Um usuário só pode ter uma conversa com outro usuário.

Quando um usuário clica em uma dessas conversas, é direcionado para uma página que mostra toda a conversa que teve com as postagens mais recentes no topo. Então, é como uma funcionalidade de bate-papo com mensagens.

Eu tenho duas mesas:

userconversationusermessages

userconversation

Contém um ID de incremento automático, que é o ID da conversa, junto com o userId e o friendId.

Quem iniciar a primeira conversa será sempre userId e o destinatário friendId, isso nunca será alterado para essa conversa.

+----+--------+----------+
| id | userId | friendId |
+----+--------+----------+

usermessages

Contém as mensagens específicas, junto com um sinalizador de leitura, a hora e a conversação

+----+---------+--------+------+------+----------------+
| id | message | userId | read | time | conversationId |
+----+---------+--------+------+------+----------------+

Como funciona

Quando um usuário vai para a mensagem de outro usuário, uma consulta será executada para verificar se ambos os usuários têm uma correspondência na tabela userconversation, se assim forconversationId é usado e a conversa continua, se não for criada uma nova linha para eles com um únicoconversationId.

Onde fica complicado

Até agora tudo está bem, no entanto, quando se trata de exibir a caixa de entrada de mensagens de todas as conversas, classificadas na última postagem, é complicado fazer uma consulta.

Para poder listar as conversas, você deve primeiro encontrar a última postagem de cada conversa, mas como você não pode fazer o pedido antes de um grupo, isso é impossível fazer com uma consulta em duas tabelas, então eu tenho que usar o seguinte:

SELECT  
    c.id,
    c.userId,
    c.friendId,
    m2.message,
    m2.read,
    UNIX_TIMESTAMP(m2.time),      
    user1.username,
    user2.username  
FROM 
    (SELECT MAX(m1.id) AS MessageID 
     FROM usermessages m1 
     GROUP BY m1.conversationId) latest_msg

INNER JOIN usermessages m2 ON latest_msg.MessageID = m2.id 
INNER JOIN userconversation c ON m2.conversationId = c.id
INNER JOIN user user1 ON c.userId = user.id
INNER JOIN user user2 ON c.friendId = user.id

WHERE c.userId = :userId OR c.friendId = :userId
ORDER BY m2.id DESC
LIMIT 10

Eu só não acho que esta é a melhor maneira que isso pode ser feito, mas não consigo pensar em outras maneiras de abordá-lo?

A tabela de banco de dados é InnoDB para acelerar as junções e melhorar a integridade dos dados, portanto não posso ter duas linhas de incremento automático.

Existe outra maneira que eu poderia me livrar da tabela userconversation e criar um Id exclusivo para colocar na coluna conversationId? Eu poderia então mover o userId e friendId para usermessages ... mas isso criaria muitos dados redundantes?