[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