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

Boeing’s unmanned A160T Hummingbird helicopter takes flight

Not the first Unmanned aerial vehicle (UAV) made. But the Boeing A160HT Humming Bird should be one of the best once further tests and simulation in combat missions is completed.
Boeing's unmanned A160T Hummingbird helicopter takes flight -

Filed under:


It's been a few ticks since we've seen Boeing send an atypical aircraft into the friendly skies, but the firm's latest helicopter has successfully completed a 12-minute test flight without so much as a pilot on board. The A160T Hummingbird unmanned rotorcraft is a turbine-powered "warfighter" that aims to provide "intelligence, surveillance, and reconnaissance coverage" in locales that could make even the most calloused veteran queasy. During its time in the air, it reportedly met every objective set for it, and while we've no idea how soon this thing will be lifting itself up, it'll eventually reach speeds of up to 140 knots and stay airborne for up to 20 hours before returning to base for a pat on the wing.

[Via The Raw Feed, image courtesy of SkyControl]

 

Read | Permalink | Email this | Comments


Office Depot Featured Gadget: Xbox 360 Platinum System Packs the power to bring games to life!

[EnGadget]