Getting data from many to many db relationships

Question

I have a table containing user comments, and I want to get the latest comment made by each user.

The request below should give an idea of ​​what I'm trying to do.

select comment, comment_id, userId FROM comments_table WHERE comment_id in ( SELECT MAX(comment_id) FROM comments_table where userId in (2001, 2002, 2010) GROUP BY userId ) 

It works on the request, but takes too much time, especially if there are many userIds.

I need a faster query expression that does the same thing.

Answer 1

Use join instead of subquery:

 SELECT b.* FROM ( SELECT userid, MAX(comment_id) AS maxcomment FROM comments_table WHERE userid IN (2001, 2002, 2010) GROUP BY userid ) a INNER JOIN comments_table b ON a.userid = b.userid AND a.maxcomment = b.comment_id 

The subselection in this query will be executed only once, unlike the WHERE IN subquery, which will be executed for each line in the comment table.

Answer 2

Try

  select comment, comment_id, userId FROM comments_table WHERE userId in (2001, 2002, 2010) order by comment_id desc limit 1 

Try to check if this query matches yours. With an index in the userid column, this should do more than decent

Answer 3

Let it be simple:

 SELECT comment, MAX(comment_id), userId FROM comments_table WHERE userId IN (2001, 2002, 2010) GROUP BY userId;