[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.4
Component:  Comments                  |     Version:  6.2.2
 Severity:  minor                     |  Resolution:
 Keywords:  has-patch has-unit-tests  |     Focuses:  performance
--------------------------------------+--------------------------

Comment (by FolioVision):

 Replying to [comment:33 spacedmonkey]:
 > Replying to [comment:32 FolioVision]:
 >
 > >
 > > As I explained in our ticket the `comment_type` **index** is what
 really helps: #58488
 > >
 >
 > I said this above and was told i was wrong.
 >
 > > I think the biggest issue here is that comment_type does not have an
 index. I feel like it really should. This will likely fix these issues.

 The `comment_type` index will help our issue of slow "All comment types"
 on the wp-admin -> Comments screen.

 @Guss77 only said that the `comment_type` index does not help with slow
 `wp_count_comments()`. Which makes sense as `comment_type` is not used in
 these queries.

 > I think the fix for this is two fold.
 >
 > Remove orderby for count to WP_Comment_Query and add a comment_type
 index. I think the index maybe have to be another ticket. Adding an index
 is not simple, as it **locks the table**.

 That's exactly right.

 In our testing adding `comment_type` index locks the table for:

 * 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 tried again on that shared server now and it took 17 seconds. The server
 load was at 2.5 to 2.7 and the VPS has 16 CPU cores and 62 GB of RAM.

 So the  `comment_type` index is really a separate issue. We started our
 ticket at #58488 and only got here as it was marked as related or partial
 duplicate. Which is true, but adding the database table index is indeed a
 different challenge.

 WordPress does use the maintenance mode during upgrades, would locking the
 table be a big obstacle in that case?

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