[wp-trac] [WordPress Trac] #31071: media / post_mime_type related queries are very slow on larger sites

[WordPress Trac]

#31071: media / post_mime_type related queries are very slow on larger sites
-------------------------------------------------+-------------------------
 Reporter:  archon810                            |       Owner:  pento
     Type:  defect (bug)                         |      Status:  assigned
 Priority:  normal                               |   Milestone:  Future
Component:  Media                                |  Release
 Severity:  normal                               |     Version:  4.1
 Keywords:  dev-feedback reporter-feedback 2nd-  |  Resolution:
  opinion has-patch                              |     Focuses:
                                                 |  performance
-------------------------------------------------+-------------------------

Comment (by tha_sun):

 Latest results from a new site running into this missing key issue again:

 Stats:  800k posts (1.3M rows), 15k users

 myslow-slow.log yields every few seconds:

 {{{
 # Query_time: 2.162829  Lock_time: 0.000109 Rows_sent: 40  Rows_examined:
 713569
 SET timestamp=1460618095;
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1
   AND (wp_posts.post_mime_type LIKE 'image/%')
   AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status =
 'inherit' OR wp_posts.post_status = 'private'))
   ORDER BY wp_posts.post_date DESC LIMIT 0, 40;
 }}}

 Explain original query:

 {{{
 mysql> EXPLAIN SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts
 WHERE 1=1
     ->   AND (wp_posts.post_mime_type LIKE 'image/%')
     ->   AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status
 = 'inherit' OR wp_posts.post_status = 'private'))
     ->   ORDER BY wp_posts.post_date DESC LIMIT 0, 40\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: wp_posts
          type: ref
 possible_keys: type_status_date
           key: type_status_date
       key_len: 82
           ref: const
          rows: 640459
         Extra: Using where; Using filesort
 1 row in set (0.00 sec)
 }}}

 Now executing:

 {{{
 mysql> ALTER TABLE wp_posts ADD INDEX post_type_mime_type (post_type,
 post_mime_type(10));

 mysql> SHOW CREATE TABLE wp_posts\G
 *************************** 1. row ***************************
        Table: wp_posts
 Create Table: CREATE TABLE `wp_posts` (
 ...
   PRIMARY KEY (`ID`),
   KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
   KEY `post_parent` (`post_parent`),
   KEY `post_author` (`post_author`),
   KEY `post_name` (`post_name`(191)),
   KEY `guid` (`guid`(191)),
   KEY `post_type_mime_type` (`post_type`,`post_mime_type`(10))
 ) ENGINE=InnoDB AUTO_INCREMENT=1364346 DEFAULT CHARSET=utf8mb4
 COLLATE=utf8mb4_unicode_ci
 1 row in set (0.00 sec)
 }}}

 still yields:

 {{{
 # Time: 160414  9:30:36
 # Query_time: 2.867190  Lock_time: 0.000184 Rows_sent: 40  Rows_examined:
 713573
 SET timestamp=1460619036;
 SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts  WHERE 1=1
   AND (wp_posts.post_mime_type LIKE 'image/%')
   AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status =
 'inherit' OR wp_posts.post_status = 'private'))
   ORDER BY wp_posts.post_date DESC LIMIT 0, 40;
 }}}

 because the new index was not actually used:

 {{{
 mysql> EXPLAIN SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts
 WHERE 1=1
     ->   AND (wp_posts.post_mime_type LIKE 'image/%')
     ->   AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status
 = 'inherit' OR wp_posts.post_status = 'private'))
     ->   ORDER BY wp_posts.post_date DESC LIMIT 0, 40\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: wp_posts
          type: ref
 possible_keys: type_status_date,post_type_mime_type
           key: type_status_date
       key_len: 82
           ref: const
          rows: 640639
         Extra: Using where; Using filesort
 1 row in set (0.00 sec)
 }}}

 Now forcing its usage via `USE INDEX`:


 {{{
 mysql> EXPLAIN SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts
   USE INDEX (post_type_mime_type)
   WHERE 1=1
   AND (wp_posts.post_mime_type LIKE 'image/%')
   AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status =
 'inherit' OR wp_posts.post_status = 'private'))
   ORDER BY wp_posts.post_date DESC LIMIT 0, 40\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: wp_posts
          type: ref
 possible_keys: post_type_mime_type
           key: post_type_mime_type
       key_len: 82
           ref: const
          rows: 689670
         Extra: Using where; Using filesort
 1 row in set (0.01 sec)

 mysql> SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts
   USE INDEX (post_type_mime_type)
   WHERE 1=1
   AND (wp_posts.post_mime_type LIKE 'image/%')
   AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status =
 'inherit' OR wp_posts.post_status = 'private'))
   ORDER BY wp_posts.post_date DESC LIMIT 0, 40\G
 ...
 40 rows in set (2.14 sec)
 }}}

 So back to square one...

 {{{
 mysql> ALTER TABLE wp_posts DROP INDEX post_type_mime_type;
 Query OK, 0 rows affected (0.08 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 mysql> ALTER TABLE wp_posts ADD INDEX post_type_mime_type_status
 (post_mime_type(10), post_type, post_status);
 Query OK, 0 rows affected (12.90 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 mysql> EXPLAIN SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts
 WHERE 1=1
   AND (wp_posts.post_mime_type LIKE 'image/%')
   AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status =
 'inherit' OR wp_posts.post_status = 'private'))
   ORDER BY wp_posts.post_date DESC LIMIT 0, 40\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: wp_posts
          type: ref
 possible_keys: type_status_date,post_type_mime_type_status
           key: type_status_date
       key_len: 82
           ref: const
          rows: 640817
         Extra: Using where; Using filesort
 1 row in set (0.00 sec)

 mysql> ALTER TABLE wp_posts DROP INDEX post_type_mime_type_status;
 Query OK, 0 rows affected (0.06 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 mysql> ALTER TABLE wp_posts ADD INDEX type_status_mime_type (post_type,
 post_status, post_mime_type(10));
 Query OK, 0 rows affected (12.52 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 mysql> EXPLAIN SELECT SQL_CALC_FOUND_ROWS  wp_posts.ID FROM wp_posts
 WHERE 1=1
   AND (wp_posts.post_mime_type LIKE 'image/%')
   AND wp_posts.post_type = 'attachment' AND ((wp_posts.post_status =
 'inherit' OR wp_posts.post_status = 'private'))
   ORDER BY wp_posts.post_date DESC LIMIT 0, 40\G
 *************************** 1. row ***************************
            id: 1
   select_type: SIMPLE
         table: wp_posts
          type: ref
 possible_keys: type_status_date,type_status_mime_type
           key: type_status_date
       key_len: 82
           ref: const
          rows: 640817
         Extra: Using where; Using filesort
 1 row in set (0.00 sec)
 }}}

 Sad panda.  Looks like the server is running into some general resource
 limitations that I need to look into first.

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