Closed Bug 445344 Opened 16 years ago Closed 16 years ago

Optimize commentslib query

Categories

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

defect
Not set
normal

Tracking

(Not tracked)

RESOLVED FIXED

People

(Reporter: laura, Assigned: laura)

References

Details

(Whiteboard: sumo_triage)

Attachments

(2 files, 1 obsolete file)

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;
Attached patch Patch to rewrite the query (obsolete) — Splinter Review
Here's the code with revised query - it's about 100x faster in my testing.
Attachment #331239 - Flags: review?(nelson)
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.
Attachment #331239 - Flags: review?(nelson) → review-
I forgot to add a DISTINCT when I killed the group by.
Attachment #331239 - Attachment is obsolete: true
Attachment #331317 - Flags: review?(nelson)
Attachment #331317 - Flags: review?(nelson) → review+
In trunk in r17404,  prod branch in r17407.
Status: ASSIGNED → RESOLVED
Closed: 16 years ago
Resolution: --- → FIXED
Whiteboard: sumo_triage
You need to log in before you can comment on or make changes to this bug.

Attachment

General

Created:
Updated:
Size: