[wp-trac] [WordPress Trac] #58368: WordPress dashboard is very slow when there are many comments (and the database isn't great)

[WordPress Trac]

#58368: WordPress dashboard is very slow when there are many comments (and the
database isn't great)
--------------------------------------+--------------------------
 Reporter:  Guss77                    |       Owner:  (none)
     Type:  defect (bug)              |      Status:  new
 Priority:  normal                    |   Milestone:  6.3
Component:  Comments                  |     Version:  6.2.2
 Severity:  minor                     |  Resolution:
 Keywords:  has-patch has-unit-tests  |     Focuses:  performance
--------------------------------------+--------------------------

Comment (by FolioVision):

 In our ticket #58488 we solve a problem with slow
 WP_Comments_List_Table::comment_type_dropdown() which is used for the
 comment type filter drop down menu on wp-admin -> Comments. This ticket
 was suggested as related.

 So we did the performance testing with the different fixes posted in this
 ticket.

 Each test was repeated 3 times, but it was running on our own staging
 server so we know nothing was interfering.

 `wp-admin-comments-slow-queries-before-1.png` shows the performance with
 the current WordPress code. That slow query to figure out if the pingbacks
 are present took about **6.7 to 7** seconds.

 `wp-admin-comments-slow-queries-with-plugin-1.png` shows performance with
 the remove-orderby-when-counting-comments.php mini-plugin - it took **7.6
 to 8** seconds.

 It does not help as WP_Comments_List_Table::comment_type_dropdown() which
 we are trying to fix does not count the comments, it only needs to see if
 there is any comment of the desired type. So that kind of a check (if the
 comments are being counted, then do not use order by) is not going to
 help.

 `wp-admin-comments-slow-queries-58368-comment-count-no-orderby-1.png`
 shows performance with the 58368-comment-count-no-orderby branch:
 https://github.com/WordPress/wordpress-develop/pull/4553 The query would
 take **7.1 to 7.8** seconds that way.

 In that pull request @peterwilsoncc tried to adjust the get_comments()
 calls in different places to remove the ordering where it's not necessary.
 But the exact single place where it matters for our issue was omitted:
 https://github.com/WordPress/wordpress-develop/pull/4571/files#diff-
 1018e4974931b2cea88b413dadc533d3cf535f977c7f3a5f310e0bc7376233a8R522

 So our fix it still needed. This is how much faster it gets - 0.7 to
 seconds, see `wp-admin-comments-slow-queries-wp-admin-comments-screen-
 performance-1.png`.

 Or it does not even appear in the list of slow queries, see `wp-admin-
 comments-slow-queries-wp-admin-comments-screen-performance-3.png`.

 Please check the fix at #58488, it's one line of code and we also had to
 add the database index for `comment_type` on `wp_comments` table.

 The server that we tested with is a 2 CPU 4 GB RAM VPS on DigitalOcean.
 Our database server is MySQL 5.7.37 and the database engine is InnoDB.

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