[wp-trac] [WordPress Trac] #14222: Improve dashboard recent comments widget performance by not fetching spam comments
WordPress Trac
wp-trac at lists.automattic.com
Thu Apr 26 04:21:24 UTC 2012
#14222: Improve dashboard recent comments widget performance by not fetching spam
comments
--------------------------------------+------------------
Reporter: Viper007Bond | Owner:
Type: defect (bug) | Status: new
Priority: normal | Milestone: 3.4
Component: Performance | Version: 3.0
Severity: normal | Resolution:
Keywords: has-patch commit already |
--------------------------------------+------------------
Changes (by nacin):
* keywords: has-patch dev-feedback => has-patch commit already
* component: Administration => Performance
Comment:
[attachment:14222.diff] introduces quite a bit of speedup.
* We're no longer checking the post status.
* We're now only fetching comment fields, no post fields.
* Even better, we're not even joining the posts table.
* We're using get_comments(), which builds the most optimized SQL
possible for these cases anyway — nice, no raw SQL at no cost.
get_comments() is hypothetically cached, as well.
* We're not selecting 50 at a time, we're selecting `10 * $items` at a
time (the first time is `5 * $items`). By default, this is 50 to show 5
comments, but 25 on the first query. With the box set to 30, this is 150
comments the first time, 300 additional times. The only way a comment does
not get included is if current_user_can('read_post') fails -- as in, if
the post is private and they don't have access. It's edge. We'll find
enough comments very quickly.
* We're doing a break 2, to back out of the foreach and while, once we
find the number of comments we're looking for.
* Cleans up some PHP-HTML context switching.
What is amazing is that we added a join plus a check of post_status =
'trash' — despite it not being needed — and then waited this long to A)
notice, and B) realize that if we're going to do that, we might as well
check comment_approved as well. Checking comment_approved and avoiding the
JOIN is, obviously, quite the performance improvement.
On a giant table (I was testing with 90,000 comments, 85,000 of them are
spam, all inserted in random order), the single query often takes under
10ms even under SQL_NO_CACHE. On a tiny table, the query isn't any
noticeable difference. The EXPLAINS are VERY reasonable. No JOIN, a SIMPLE
query, and a key is even used (though by no means necessary, per my
testing).
{{{
EXPLAIN SELECT * FROM blank_comments WHERE ( comment_approved = '0' OR
comment_approved = '1' ) ORDER BY comment_date_gmt DESC LIMIT 150;
+----+-------------+----------------+-------+---------------------------+---------------------------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys |
key | key_len | ref | rows | Extra
|
+----+-------------+----------------+-------+---------------------------+---------------------------+---------+------+------+-----------------------------+
| 1 | SIMPLE | blank_comments | range | comment_approved_date_gmt |
comment_approved_date_gmt | 62 | NULL | 2583 | Using where; Using
filesort |
+----+-------------+----------------+-------+---------------------------+---------------------------+---------+------+------+-----------------------------+
}}}
If we were to do the comment_approved calculations in PHP, sure, we get a
slightly better query, but it doesn't end up being any quicker on small
tables, and on large tables we are very likely to end up making dozens of
these queries (at LIMIT 50).
{{{
EXPLAIN SELECT * FROM blank_comments ORDER BY comment_date_gmt DESC LIMIT
150;
+----+-------------+----------------+-------+---------------+------------------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra |
+----+-------------+----------------+-------+---------------+------------------+---------+------+------+-------+
| 1 | SIMPLE | blank_comments | index | NULL |
comment_date_gmt | 8 | NULL | 150 | |
+----+-------------+----------------+-------+---------------+------------------+---------+------+------+-------+
}}}
After looking at this for a year, I am strongly confident in this.
--
Ticket URL: <http://core.trac.wordpress.org/ticket/14222#comment:24>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software
More information about the wp-trac
mailing list