445344 - Optimize commentslib query

Status: RESOLVED FIXED

(support.mozilla.org :: General, defect)

Description

[Laura Thomson :laura]

From: https://bugzilla.mozilla.org/show_bug.cgi?id=425135#c40

Rewrite this query in commentslib to use subselects
select a.`threadId`,a.`object`,a.`objectType`,a.`parentId`,    
a.`userName`,a.`commentDate`,a.`hits`,a.`type`,a.`points`,    
a.`votes`,a.`average`,a.`title`,a.`data`,a.`hash`,a.`user_ip`,    
a.`summary`,a.`smiley`,a.`message_id`,a.`in_reply_to`,a.`comment_rating`,
IFNULL(max(b.`commentDate`), a.`commentDate`)  as `lastPost`,    
count(b.`threadId`) as `replies`, (a.`type` = 's') AS sortorder from
`tiki_comments` a left join `tiki_comments` b  on b.`parentId`=a.`threadId`
where a.`object`='1' and a.`objectType` = 'forum' and a.`parentId` = 0   group
by
a.`threadId`,a.`object`,a.`objectType`,a.`parentId`,a.`userName`,a.`commentDate`,a.`hits`,a.`type`,a.`points`,a.`votes`,a.`average`,a.`title`,a.`data`,a.`hash`,a.`user_ip`,a.`summary`,a.`smiley`,a.`message_id`,a.`in_reply_to`,a.`comment_rating`
order by sortorder DESC, `hits` asc, `threadId` LIMIT 180,20;

Comment 1

[Laura Thomson :laura]

Attachment: Patch to rewrite the query

Here's the code with revised query - it's about 100x faster in my testing.

Comment 2

[Nelson Ko [:nkoth]]

Comment on attachment 331239 [details] [diff] [review]
Patch to rewrite the query

When I try this patch on my server, the forum topics returned have a lot of duplicated entries per thread (topic). See attachment.

Comment 3

[Nelson Ko [:nkoth]]

Attachment: screenshot with duplicated threads in listing

Comment 4

[Laura Thomson :laura]

Attachment: Revised patch with DISTINCT

I forgot to add a DISTINCT when I killed the group by.

Comment 5

[Laura Thomson :laura]

In trunk in r17404,  prod branch in r17407.