[wp-trac] [WordPress Trac] #22301: Performance problem with Recent Comments widget

[WordPress Trac]

#22301: Performance problem with Recent Comments widget
-----------------------------+--------------------------
 Reporter:  pento            |       Type:  defect (bug)
   Status:  new              |   Priority:  normal
Milestone:  Awaiting Review  |  Component:  Performance
  Version:  trunk            |   Severity:  normal
 Keywords:  dev-feedback     |
-----------------------------+--------------------------
 When a comment is posted (or the status of a comment changes), the
 `widget_recent_comments`cache item is invalidated, which the Recent
 Comments widget uses to populate the widget content. On the next widget
 display, it will call `get_comments()` to repopulate the cache.

 The problem occurs when you have a very large number of comments, the
 MySQL query will use the `(comment_approved, comment_date_gmt)` index, but
 if MySQL has to scan too many rows in an index, it'll switch to table scan
 instead. As the `comment_approved` column is mostly the same value, this
 will almost always happen. This is compounded by the query occurring on
 every page load until the cache is re-populated - if the query takes 60
 seconds to run, there could potentially be hundreds of instances of the
 same query running.

 So, we need a solution that either hides or eliminates how slow this query
 can be, and only runs it (at most) once per new comment.

 After discussing this with @matt, we have a couple of ideas:

 1. Move this query to a `wp_schedule_single_event()` call, which has the
 bonus of ensuring only one is scheduled at any given time. The downside is
 that it may cause the cache to be outdated on a low traffic site.

 2. Keep a queue of recent comments in cache, and push a new one onto the
 queue when posted. This avoids the query entirely, but there would be a
 race condition if two comments were posted at nearly the same time - one
 of them could be left out of the queue entirely.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/22301>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software