Improve performance of wp-admin -> Comments

[FolioVision]

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:

get_comments( array(
  'number' => 1,
  'type'   => $type,
) );

That code runs query like:

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:

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

wp-admin -> Comments screen is slowed down by the inefficient way of getting the comment types for the comment type drop down filter.

To fix that we:

• improve the PHP code to create a simpler MySQL query.
• add comment_type index for wp_comments

This has reduced our wp-admin -> Comments load times by 85%.

Please see this ticket for full details and performance testing https://core.trac.wordpress.org/ticket/58488

[johnbillion]

Related / partial dupe: #58368

[DrewAPicture]

Looks like the orderby => 'none' improvement was already committed as part of #58368. @FolioVision you'll need to update your PR to just cover the schema change proposal as there's now a merge conflict.