[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 Guss77):

 Replying to [comment:25 spacedmonkey]:
 > I think the biggest issue here is that comment_type does not have an
 index.

 To provide more actual info, on why indexing doesn't (and can't) help,
 here is how the comments table currently looks - with tons of indexes:

 {{{
 Create Table: CREATE TABLE `wp_xp7b48_comments` (
   `comment_ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
   `comment_post_ID` bigint(20) unsigned NOT NULL DEFAULT '0',
   `comment_author` text COLLATE utf8mb4_unicode_ci,
   `comment_author_email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT
 NULL,
   `comment_author_url` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT
 NULL,
   `comment_author_IP` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT
 NULL,
   `comment_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `comment_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
   `comment_content` mediumtext COLLATE utf8mb4_unicode_ci,
   `comment_karma` int(11) NOT NULL DEFAULT '0',
   `comment_approved` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT '1',
   `comment_agent` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
   `comment_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT
 'comment',
   `comment_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
   `user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
   `comment_subscribe` enum('Y','N') COLLATE utf8mb4_unicode_ci NOT NULL
 DEFAULT 'N',
   `comment_mail_notify` tinyint(4) NOT NULL DEFAULT '0',
   PRIMARY KEY (`comment_ID`),
   KEY `comment_post_ID` (`comment_post_ID`),
   KEY `comment_approved_date_gmt` (`comment_approved`,`comment_date_gmt`),
   KEY `comment_date_gmt` (`comment_date_gmt`),
   KEY `comment_parent` (`comment_parent`),
   KEY `user_id` (`user_id`),
   KEY `comment_type` (`comment_type`),
   KEY `comment_approved` (`comment_approved`),
   KEY `comment_date` (`comment_date`),
   KEY `comment_author_email` (`comment_author_email`(10)),
   KEY `opt_last_commented_posts`
 (`comment_post_ID`,`comment_approved`,`comment_type`,`comment_ID`)
 ) ENGINE=MyISAM AUTO_INCREMENT=12253647 DEFAULT CHARSET=utf8mb4
 COLLATE=utf8mb4_unicode_ci
 }}}

 As you can see, there is an index on `comment_type`, but it isn't useful
 for the query in question - mostly because `wp_count_comments()` doesn't
 even look at `comment_type` - as shown in the ticket description.

 Running `EXPLAIN` shows us that the query **does** use indexes - it just
 isn't helpful when you are loading literally 100% of the rows and then
 needs to sort them "in memory" on a server without enough memory:

 {{{
 mysql> explain SELECT COUNT(*) FROM wp_xp7b48_comments WHERE (
 comment_approved = '1' )
   ORDER BY wp_xp7b48_comments.comment_date_gmt DESC \G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: wp_xp7b48_comments
    partitions: NULL
          type: ref
 possible_keys: comment_approved_date_gmt,comment_approved
           key: comment_approved
       key_len: 83
           ref: const
          rows: 5831275
      filtered: 100.00
         Extra: NULL
 }}}

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