[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:  peterwilsoncc
     Type:  defect (bug)  |      Status:  assigned
 Priority:  normal        |   Milestone:  6.3
Component:  Comments      |     Version:  6.2.2
 Severity:  minor         |  Resolution:
 Keywords:  has-patch     |     Focuses:  performance
--------------------------+----------------------------

Comment (by Guss77):

 Replying to [comment:44 Guss77]:
 > Unfortunately, I'm not using MySQL 8, and - as I reported in the
 original ticket description - for the first query that you tested, the
 database I use does not discard the unneeded `ORDER BY` and that massively
 slows it down.

 A small correction - I'm not sure how to tell whether the database
 optimizer discarded the unneeded `ORDER BY`, but the query @peterwilsoncc
 listed first, i.e.:

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

 Completes in a more or less reasonable time on my system (around a second
 - not great, but manageable). The problem I have - as reported in the
 ticket description - is with a slightly different query:

 {{{
 SELECT COUNT(*)
 FROM wp_comments
 WHERE ( comment_approved = '1' )
 ORDER BY wp_comments.comment_date_gmt DESC
 }}}

 This one - which is what `wp_count_comments()` does - runs 20 times
 slower.

 Here's the `EXPLAIN` result of the first one:

 {{{
 type: range
 possible_keys: comment_approved_date_gmt,comment_approved
 key: comment_approved_date_gmt
 ref: NULL
 filtered: 100
 Extra: Using where; Using index
 }}}

 and here's the `EXPLAIN` results for the second - slower - one:

 {{{
 type: ref
 possible_keys: comment_approved_date_gmt,comment_approved
 key: comment_approved
 ref: const
 filtered: 100
 Extra: NULL
 }}}

 and this is the `EXPLAIN` results for the second query (the problematic
 one, in my case) where the `ORDER BY` clause was manually removed:

 {{{
 type: ref
 possible_keys: comment_approved_date_gmt,comment_approved
 key: comment_approved
 ref: const
 filtered: 100
 Extra: Using index
 }}}

 I'm not a database expert, but I think what it means is that for the first
 version (checking for all possible values of `comment_approved`), the
 database figures out that the `WHERE` clause is meaningless and discards
 it - quickly returning all rows. It probably doesn't bother with ordering.
 For the second version - the slow one, it knows there's some work to do,
 tries to use the `comment_approved` index - probably ignoring the `ORDER
 BY` clause (I think?) - but then, for some reason, doesn't actually uses
 the index. I think it scans the table doing extra work and running out of
 cache. For the third - and faster - version it suddenly can use the
 `comment_approved` index, even though the only change is the removal of
 the `ORDER BY`.

 BTW, this is on MySQL 5.7.

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