[MYSQL Snippet] Update "CountComments"-value for every Discussion

oliverraduner · January 2011

Today I imported roughly 1'100 discussions & about 17'000 comments to my current Vanilla installation.

To sweeten this up even more, I was able to discover an SQL-snippet in the web, that loops through the GDN_Discussion table & updates the "CountComments"-value (amount of Comments per Discussion) for every single Discussion.

I thought I share it here, as it might be helpful for somebody else importing Discussions & Comments to Vanilla...

Beware: this is a direct on-database command - use on your own risk!!

UPDATE GDN_Discussion
SET CountComments = (
    SELECT SUM(GDN_Comment.DiscussionID = GDN_Discussion.DiscussionID)
    FROM GDN_Comment
);

Cheers

Pixelnated · January 2011

if you want to improve the performance on a query like that try changing it to something like this.

update GDN_Discussion D 
  JOIN GDN_Comment C 
    ON D.DiscussionID = C.DiscussionID
 SET D.CountComments = (SELECT count(C.DiscussionID) as comment_count
                          FROM GDN_Comment C 
                         where D.DiscussionID = C.DiscussionID )

---
Also if you want to test the results first just adjust it to be like this

select d.DiscussionID, count(*)
from GDN_Discussion D
   , GDN_Comment C
where C.DiscussionID = D.DiscussionID
group by d.DiscussionID
order by 1;

UnderDog · February 2011

Added code tags for readability