jake on 2007.05.12
at 04:33 pm
While working on Kate’s site recently I stumbled upon an issue with the SRG Clean Archives plugin I installed last year. When the plugin is displaying the archive it makes a lot of extra calls to the database. This slows down the page a lot just to display the number of comments a post has. And the more posts you have the slower it gets.
My solution is to simply add a join to the second query so that it grabs the comment count for each post in one call. To implement this fix you replace the second query with the following (currently line 32).
$arcresults2 = $wpdb->get_results("SELECT ID, post_date, post_title, comment_status, count(".$wpdb->comments.".comment_id) as comments_count FROM " . $wpdb->posts . " LEFT JOIN ".$wpdb->comments." ON ".$wpdb->posts.".id=".$wpdb->comments.".comment_post_ID AND (".$wpdb->comments.".comment_approved='1') WHERE post_date LIKE '$thisyear-$thismonth-%' AND $current_posts AND post_status='publish' AND post_password='' group by ID ORDER BY post_date DESC");
Now you get
$arcresult2->comments_count) just like before with the single call. The final step to showing the count is to uncomment the code toward the bottom, remember you only need the
ifstatement and not the extra queries, and replace
I sent these code updates to the original author Shawn Grimes. He has passed the plugin’s maintenance to another Sean so I thought sharing would be nice for both the new author and the general public. Please let me know if I let any bugs through or if anything is unclear. Thanks to Shawn and Sean for building this plugin in the first place.
Posted in: Programming · Web
Sean said on 2007.05.12 at 09:18 pm
SRG Clean Archives
Hi Jake. Thanks for your feedback and suggestion for the plugin.
I actually have a new release coming out hopefully in a week (2 max) after some more testing but I’ll be adding in your adjustment.
I’m also going to send you a follow up email.
Thanks again for your support of the plugin.
Comments have been automatically disabled to curtail spam.