**UPDATE**: After further research of the query below, I have found some additional information. The query was first introduced in WP 2.3 and is used for comment flood protection. I am currently running WP 2.9.1 and do not have the issue as the query has been modified and using a proper index to assist with the query speed. If you are using the latest version of WP, you won’t have this problem. I am still trying to find exactly when the query was changed so you all can know where you stand. However, I know that some people don’t upgrade their WP version due to changes they have made which upgrading will break. If you are on an older version of WP and have a large number of comments (tens to hundreds of thousands), this query will help improve your comment post times. If you are familiar enough with MySQL, look at the wp_comments table in your database. If there is an index on the `comment_date_gmt` column, you are ok. If not, read on, and run the query below as adding an index to the `comment_date_gmt` column won’t work as your query does not have that column in it’s WHERE clause.

One of daily responsibilities as a database administrator is maintaining the health of our shared hosting environment. In doing so, I deal with plenty of WordPress blogs daily. The one thing I have noticed is that many WordPress plugins are very poorly designed and can cause problems in a shared hosting environment. WordPress at it’s core though, is very well designed, although I have noticed some areas of possible improvement. As I come across common issues, I will post the fixes for them here for all to use.

The following is one of the most common queries I see all the time that actually comes from the WordPress core:
SELECT comment_date_gmt
FROM wp_comments
WHERE comment_author_IP = '94.142.131.22' OR
comment_author_email = 'yigrzds@tkgmd.com'
ORDER BY comment_date DESC
LIMIT 1

The IP and email is obviously only sample data. On the example server, the MySQL Explain for the query showed the following:

id: 1
select_type: SIMPLE
table: wp_comments
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 76845
Extra: Using where; Using filesort

Now, a query that examines 76,000+ rows is not the end of the world, but with enough traffic, it can slow down the server immensely, mainly because the explain above indicates that no key is being used on the query and therefore a full table scan is being performed.

To help alleviate this, the following code can be executed from a MySQL command prompt, or a management type interface like phpMyAdmin:

mysql> ALTER TABLE wp_comments ADD INDEX(comment_author_IP), ADD INDEX(comment_author_email)

**Note: The above optimization assumes the default database prefix of “wp”. If your database prefix is different, replace “wp” in the above query with your databases prefix.**

Afterward, the explain on the exact same query returns the following:

id: 1
select_type: SIMPLE
table: wp_comments
type: index_merge
possible_keys: comment_author_IP,comment_author_email
key: comment_author_IP,comment_author_email
key_len: 302,302
ref: NULL
rows: 2139
Extra: Using union(comment_author_IP,comment_author_email); Using where; Using filesort

As you can see, the query now examines 2139 rows as opposed to 76,000+ by using an Index Merge, which means the query is pulling data using two different indexes on the same query. Now keep in mind, index merges are only available in MySQL 5.0+. If you are using MySQL 4.1 or below, an index merge will not work, and I would suggest adding an index on the comment_author_IP field as it will tend to have more unique values. Simply remove everything after the comma in the above command.

This optimization will improve the speed of the query on your database and will provide better performance on your WordPress blog. Please share your thoughts below on this hack and the results for you.

Check out these Related Posts