CODEWITHSUNDEEP
mysqlsqldatabaseperformance
v4_adi
v4_adi

Reputation: 1533

Select latest comment on post,total count from followers without index on comment string

I need to fetch posts from followers, with total comment count on each post, each post latest comment. Following is my table structure sample

post_table
post_id | post_body | post_author | post_date
   1    |     Hi    |      2      | 1970-01-01 05:30:00

Comment table
comment_id | comment_body | entity_id | comment_date | comment_author
     1     |  Hi comment  |      1    |  1970-01-01  |        1

I have index on post_id, comment_id, entity_id and date.

I have tried following query 

select    p1.author_id,
          p1.post_id,
          count(comment1.entity_id) as comment_count,
          display_name
from      post as p1
left join comments as comment1 
on        p1.post_id = comment1.entity_id 
left join user_details as u1 
on        p1.post_id = u1.uid
where     p1.author_id in (select user_relation.follower_id 
                           from   user_relation 
                           where follower_id = '6')
group by  p1.post_id

I want to fetch comment body of latest comment on each post without doing group by on comment body as I don't want to have index on large string. I have already tried looking on net and other so questions.

Upvotes: 0

Views: 48

Answers (1)

Gurwinder Singh
Gurwinder Singh

Reputation: 39507

You could find the comment count and latest comment id per post in one go and then join it with post and comment table to get the required details:

select p.author_id, p.post_id, c.comment_count, c2.comment_body
from post p
left join (
    select entity_id, count(*) as comment_count, max(comment_id) as max_comment_id
    from comments
    group by entity_id
) c on p.post_id = c.entity_id
left join comments c2 on c2.comment_id = c.max_comment_id;

Upvotes: 1

Related Questions