[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:
     Type:  enhancement                          |  markjaquith
 Priority:  normal                               |      Status:  accepted
Component:  Comments                             |   Milestone:  Future
 Severity:  major                                |  Release
 Keywords:  needs-testing dev-feedback needs-    |     Version:  3.3
  refresh                                        |  Resolution:
                                                 |     Focuses:
                                                 |  performance
-------------------------------------------------+-------------------------

Comment (by FolioVision):

 Since InnoDB is the default database engine of MySQL since version 5.5.5
 we did our test again on a site with 400,000 comments.

 '''MyISAM''':

 The single query: 0.24-0.28 seconds
 Using multiple queries (our fix): 0.10-0.14 seconds

 '''InnoDB''':

 The single query: 0.15-0.20 seconds
 Using multiple queries: 0.1 - 0.16 seconds

 Another test on a site with 826,000 comments running on '''WP Engine with
 InnoDB''':

 The single query: 0.26-0.30 seconds
 Using multiple queries: 0.18 - 0.22 seconds

 So that contradicts what @ryan posted:
 https://core.trac.wordpress.org/ticket/19901#comment:14 The solution with
 multiple queries is faster than a single query.

 I'm also noticing there is '''another query''' which looks kind-of like a
 duplicate:

 {{{#!php
 <?php
 SELECT COUNT(*) FROM wp_comments  WHERE ( ( comment_approved = '0' OR
 comment_approved = '1' ) )  ORDER BY wp_comments.comment_date_gmt DESC
 }}}

 It's called by WP_Comment_Query. I'm not sure if it's used for paging or
 something like that. Normally it doesn't slow down the site much, as that
 table is already in cache, but it's something to look into.


 Thanks,
 Martin Vicenik

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