Improve WordPress Comments Load time when using Akismet: Adding an index to wp_comments

A customer of mine was seeing a 15 second load time on their WordPress site when clicking on “Comments”.

I installed Query Monitor and saw that the Akismet plugin was running the following SQL command on each comment.

SELECT COUNT(*) FROM wp_comments WHERE user_id = 1820 AND comment_approved = 1;

This query was to show how many comments the user had previously had approved. The query was taking 0.5 seconds per comment listed when you visit the “Comments” page. The page was taking around 15 seconds to load at times. Now if you had this page set to load 100 comments per page, then you’re looking at 60 seconds.

I decided to run an explain on the query to figure out what was going on.

explain SELECT COUNT(*) FROM wp_comments WHERE user_id = 778 AND comment_approved = 1;

Which resulted in the following. (Sorry for the screenshot, copy/pasting and reformatting was not something I wanted to do. Also this references the wp_bspr_comments. This is due to the database having a wp_bspr for it’s table prefix.

MySQL Explain on wp_comments query not indexed

As you can see, the query is having to go through 19866 rows within the wp_comments table which is the total amount in the table. Since they’re using count(*) all rows are scanned.

So I decided to add an index for the user_id and comment_approved columns.

create index wp_comments_askismet ON wp_comments (user_id,comment_approved);

No when running an explain, there are only 9 rows scanned.

MySQL Explain on wp_comments with Aksimet after index

Load times were reduced dramatically. However, this can all be mitigated if you simply turn of the Akismet feature.

Akismet option to show number of approved comments beside each comment author.

0 Shares:
You May Also Like

Want a Google Reader equivalent, then check out Tiny Tiny RSS 1.2.12

When I firsted looked at the this application, I thought it was going to look ugly and have no functionallity. I mean after looking at Google Reader and using it for some time, it really was another good Google Application. And it was made to make it easy to read and sort RSS feeds. One look at the demo, and I was hooked. It has some common features that Google Reader does, starred items and labels. I just wonder if you can plop and authentication/user account creation on it so that you can offer your own branded reader similar to Googles. TinyTiny RSS 1.2.12 Demo!
Tiny Tiny RSS 1.2.12 (Default branch) - Screenshot Tiny Tiny RSS is a Web-based news (RSS, RDF, or Atom) feed aggregator designed to allow you to read news from any location, while feeling as close to a real desktop application as possible.
License: GNU General Public License (GPL)
Changes:
This release fixes reported bugs, adds the zh_CN translation, and adds various minor improvements.

[FreshMeat]

SueTube: sex, copyright, and rock & roll

Give this a gander. All of the current lawsuits against YouTube/Google, when they started taking down videos of Russel Peters and other popular shows. I cried.
SueTube: sex, copyright, and rock & roll -

Since its launch, YouTube has been a magnet for controversy. Ars looks at the popular video site's history of threats, lawsuits, and bans with an eye towards understanding what the landscape will look like once the dust settles.

Read More...

[Ars Technica]

AMD 64 2000+ vs Intel’s Atom: AMD’s 8-watt processor outperforms Intels Atom

Intel's Atom processor and AMD's 64 2000+ were pitted against each other. And the results were for once in favor of AMD.
In our Munich lab’s duel of the energy-savers, the AMD Athlon 64 2000+ beats the Intel Atom 230 in energy consumption and processing power. Each of the systems was based on a desktop platform. The Achilles heel of the Intel system is its old system platform with the 945GC chipset, while AMD offers a more modern 780G platform. The energy-saving solution from AMD offers more possibilities: it has three times as many SATA ports, possesses better onboard graphics performance, and can also support two monitors. Unlike the Intel solution, an HD resolution (1920x1200) with high picture quality is possible through DVI/HDMI ports. And early information suggests that the AMD Athlon 64 2000+ should cost close to $90.
Read the full article at tomshardware.com