getting data from a many to many db relationship

Question

I have a table that holds user comments and I want to retrieve the last comment made by each user.

Query below should give u an idea of what i am 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
)

Above query works but takes too long especially if there are many userIds.

I need a quicker query statement that accomplishes the same thing.

Answer 1

Use a join instead of a 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 sub-select in this query will only execute once, as opposed to a WHERE IN subquery which will execute for each row in the comments table.