Need feedback on an SQL Request (MySQL, GROUP BY behavior)

Question

I'm programming a private conversation system and need advices on my most complex request.

My (simplified) tables, under MySQL 5.5 :

user:
    id (int)
    username (string)

conversation:
    id (int)
    subject (string)

conversation_user:
    conversation_id (int)
    user_id (int)

message:
    id (int)
    conversation_id (int)
    user_id (int)
    content (text)
    created_at (datetime)

So, I want to display to the user the list of the conversations in which he is participating : for each conversation, I need the conversation id, the conversation subject, the list of participating users, the last message id, the last message author id and username, and the last message date.

I've written this request, which looks pretty bad :

SELECT
    cu.conversation_id,
    c.subject,
    u.username,
    m.id,
    m.user_id,
    m.created_at
FROM conversation_user cu
JOIN conversation c ON c.id = cu.conversation_id
JOIN conversation_user cu2 ON cu2.conversation_id = c.conversation_id
JOIN user u ON u.id = cu2.user_id
JOIN message m ON m.conversation_id = cu.conversation_id
WHERE cu.user_id = :current_user_id # the session user_id
AND m.id = (SELECT MAX(id) FROM message WHERE conversation_id = cu.conversation_id)

So, I would like to know if you guys see a better way to get the same result ?

I've already read GROUP BY behavior when no aggregate functions are present in the SELECT clause, that's why I didn't put a GROUP BY clause and wrote the last line instead : AND m.id = (SELECT MAX(id) FROM message WHERE conversation_id = cu.conversation_id)

Thanks !

Edit

I did an EXPLAIN on this request, and there is no KEY used for the JOIN user u ON u.id = cu2.user_id line : why ?

(the first question is still relevant)

http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html — Karolis, Oct 05 '11 at 15:18
Nice link, thanks, I'll try to make a sub JOIN and post the new request — Nanocom, Oct 05 '11 at 15:24

MatBailie · Accepted Answer · 2011-10-05T15:19:50.430

1

There are alternatives, such as I'm about to put forward for you. But, honestly, that's how I'd do it :)

AND m.id = (SELECT id FROM message WHERE conversation_id = cu.conversation_id ORDER BY id DESC LIMIT 1)

The above allows you to order by DATE or somet other field, and still pick just one message id.

JOIN
  message m
    ON m.conversation_id = cu.conversation_id
JOIN
  (SELECT conversation_id, MAX(id) AS id FROM message GROUP BY conversation_id) AS filter
    ON  filter.conversation_id = cu.conversation_id
    AND filter.id = m.id

The above avoids a correlated sub-query, and so can (but not always) be faster.

In terms of there be no key being used for JOIN user u ON u.id = cu2.user_id, do both of the two tables in question have indexes on the relevant fields?

edited Oct 05 '11 at 15:19

answered Oct 05 '11 at 14:52

MatBailie

83,401
18
103
137

Yeah, this is nearly the same and maybe faster. So there are no other ways to do that than a bad subquery ? – Nanocom Oct 05 '11 at 15:12
Every id or *_id column is indexed, yeah. The column id of table user is primary key. – Nanocom Oct 05 '11 at 15:25

Need feedback on an SQL Request (MySQL, GROUP BY behavior)

Edit

1 Answers1