sql - Don't grab duplicates (MYSQL DISTINCT GROUP BY) -

sql - Don't grab duplicates (MYSQL DISTINCT GROUP BY) -

i have conversation-based message system. (messages viewed forum thread. akin gmail.) tables:

convos (primary cid) messages (primary mid) users (primary uid)

each row in convos thread. each row in messages reply, including cid belongs to. load conversation grabbing x recent messages cid. bring together users table messages on uid. (each message has uid of sender.) users user_name, avatar. illustrate, basic query this:

select messages.body, users.avatar messages bring together users on (users.uid=messages.uid) messages.cid=1

if same user makes many replies i'm grabbing avatar several times, varchar (a link avatar image). performance want grab each avatar once. i'm thinking sub query group by or distinct

update

i'm going robby c's idea. have public chats user can reply , replies grow. show 20 replies per page. avatars needed 20 replies this:

select u.avatar from( select uid,cid messages cid=1234 order date desc limit 20 ) m bring together users u on (u.uid=m.uid) grouping u.uid

subquery 20 recent replies, user info them join, group users.uid cuts duplicates. seems work well.

i don't think subquery you're considering improve performance. easier , more performant 2 resultsets , match them in code using uid.

the query message contents:

select messages.body, messages.uid messages messages.cid=1;

and query avatars:

select distinct users.uid, users.avatar messages bring together users on (users.uid = messages.uid) messages.cid=1;

in terms of query execution time, don't think see improvement compared single query had. might able cut down io and/or network usage.

if find getting same user information, it's worthwhile consider caching entire user table in memory.

mysql sql group-by distinct