[wp-trac] [WordPress Trac] #58488: Improve performance of wp-admin -> Comments

[WordPress Trac]

#58488: Improve performance of wp-admin -> Comments
----------------------------+-----------------------------
 Reporter:  FolioVision     |      Owner:  (none)
     Type:  defect (bug)    |     Status:  new
 Priority:  normal          |  Milestone:  Awaiting Review
Component:  Administration  |    Version:  trunk
 Severity:  normal          |   Keywords:
  Focuses:  performance     |
----------------------------+-----------------------------
 Our wp-admin -> Comments screens just got slower over time and the reason
 is the "All comment types" drop down filter.

 Here's how much that single select box adds on some of our websites
 depending on the number of comments:

 * 6 seconds for website with 2,668,659 comments
 * 1 second for website with 795,581 comments

 So it's really too much delay anytime you open wp-admin -> Comments just
 to get that single filtering option.

 The problematic code is in the
 WP_Comments_List_Table::comment_type_dropdown() function which calls:

 {{{#!php
 get_comments( array(
   'number' => 1,
   'type'   => $type,
 ) );
 }}}

 That code runs query like:

 {{{#!sql
 SELECT wp_comments.comment_ID FROM wp_comments
   WHERE
     ( ( comment_approved = '0' OR comment_approved = '1' ) ) AND
     comment_type IN ('pingback', 'trackback')
   ORDER BY wp_comments.comment_date_gmt DESC LIMIT 0,1
 }}}

 As illustrated that query can take seconds to finish - as much as 6
 seconds in our case and we have to wait that long anytime we open wp-admin
 -> Comments.

 There are two causes of the poor performance:

 === **1) Missing comment_type index**

 Running SQL EXPLAIN command on the query reveals that it only uses the
 comment_date_gmt index.

 Adding the comment_type index helps: ALTER TABLE `wp_comments` ADD INDEX
 `comment_type` (`comment_type`)

 Here's how much time adding that index took:

 * 5 seconds for 2,668,659 comments (dedicated SQL server with low load)
 * 23 second for 795,581 comments (shared server with higher load)

 I see the wp-admin/includes/upgrade.php does a fair amount of ALTER TABLE
 with ADD INDEX.

 We prepared that upgrade code in wp-admin/includes/upgrade.php.

 And of course added that index for new install in wp-
 admin/includes/schema.php.

 === **2) Using ORDER BY when not required**

 On one of our databases (using MariaDB) just adding the comment_type index
 fixed the issue, but on some other we **also** had to get rid of the ORDER
 BY in the query as it's not important when you are checking if there are
 any Pings.

 get_comments() just does that automatically. This can be easily fixed by
 adding 'oderby' => 'none' to the call in
 WP_Comments_List_Table::comment_type_dropdown() in wp-admin/includes
 /class-wp-comments-list-table.php:

 {{{#!php
 get_comments(
     array(
         'number'  => 1,
         'orderby' => 'none',
         'type'    => $type,
     )
 )
 }}}

 This fix can be seen in the  Github pull request.

 ---

 With both fixes the performance is now much better:

 * 0.4 seconds for website with 2,668,659 comments
 * 0.12 seconds for website with 795,581 comments

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/58488>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform