[wp-trac] [WordPress Trac] #19901: Speeding up Dashboard and Comment moderation SQL load

[WordPress Trac]

#19901: Speeding up Dashboard and Comment moderation SQL load
----------------------------------------+-----------------------------
 Reporter:  FolioVision                 |       Owner:  markjaquith
     Type:  enhancement                 |      Status:  accepted
 Priority:  normal                      |   Milestone:  Future Release
Component:  Comments                    |     Version:  3.3
 Severity:  major                       |  Resolution:
 Keywords:  needs-testing dev-feedback  |     Focuses:  performance
----------------------------------------+-----------------------------
Changes (by FolioVision):

 * keywords:  needs-testing dev-feedback needs-refresh => needs-testing dev-
     feedback


Comment:

 In the patch which I just added the current logic for comment counting was
 moved into the `if ( $post_id > 0 ) {` statement to keep the logic the
 same when querying the number of comments for a single post and in `else`
 there is the new logic for a global comment count - which uses multiple
 SQL queries as discussed.

 I tested the old code (using `group by`) and the comments were counted in
 0.415 seconds. With the new code it's 0.267 seconds. I did 5 test
 repetitions for each case using MySQL with InnoDB engine and no SQL
 caching (we can't count on it as on a live website there are query results
 to store there for front-end etc.). The number of comments was 916,480. On
 a server with high load these numbers would jump up much higher, so the
 savings here worth it - imagine it's 2.67 seconds instead of 4.15 seconds
 or worse.

 When using with a specific `$post_id` there are no speed benefits, so it's
 probably better to just keep the SQL count down for it, as this function
 might be also used also in front-end on comment submission etc.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/19901#comment:25>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform