[wp-trac] [WordPress Trac] #27324: increase performance of table joins on wp_users and wp_posts

[WordPress Trac]

#27324: increase performance of table joins on wp_users and wp_posts
-------------------------+-----------------------------
 Reporter:  NAPPA        |      Owner:
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Database     |    Version:  3.8.1
 Severity:  normal       |   Keywords:
  Focuses:  performance  |
-------------------------+-----------------------------
 Today I found a slow query running on a client wordpress database.

 {{{
 mysql> explain SELECT    u.ID,    count(post_author) as posts  FROM
 wp_posts as p    LEFT JOIN wp_users as u ON p.post_author = u.ID  WHERE
 p.post_status = 'publish'    AND u.ID != ''    AND p.post_type = 'post'
 GROUP BY    p.post_author  HAVING    COUNT(post_author) > 1 ;
 +----+-------------+-------+--------+------------------------------+------------------+---------+------------------------------+------+---------------------------------------------------------------------+
 | id | select_type | table | type   | possible_keys                | key
 | key_len | ref                          | rows | Extra
 |
 +----+-------------+-------+--------+------------------------------+------------------+---------+------------------------------+------+---------------------------------------------------------------------+
 |  1 | SIMPLE      | p     | ref    | type_status_date,post_author |
 type_status_date | 124     | const,const                  | 7731 | Using
 index condition; Using where; Using temporary; Using filesort |
 |  1 | SIMPLE      | u     | eq_ref | PRIMARY                      |
 PRIMARY          | 8       | fakefake_blogg.p.post_author |    1 | Using
 index                                                         |
 +----+-------------+-------+--------+------------------------------+------------------+---------+------------------------------+------+---------------------------------------------------------------------+
 2 rows in set (0.00 sec)

 mysql> SELECT    u.ID,    count(post_author) as posts  FROM    wp_posts as
 p    LEFT JOIN wp_users as u ON p.post_author = u.ID  WHERE
 p.post_status = 'publish'    AND u.ID != ''    AND p.post_type = 'post'
 GROUP BY    p.post_author  HAVING    COUNT(post_author) > 1 ;
 +------+-------+
 | ID   | posts |
 +------+-------+
 |    3 |  2739 |
 |    8 |     8 |
 |   10 |   115 |
 |   14 |    10 |
 |   34 |  1575 |
 |   40 |  3600 |
 |   41 |    38 |
 |   42 |    19 |
 |   45 |     4 |
 |   47 |   217 |
 |   48 |   129 |
 +------+-------+
 11 rows in set (0.05 sec)
 }}}

 The enhancement :


 {{{


 mysql> CREATE INDEX imran_status_type_author ON wp_posts
 (post_status,post_type,post_author) ;
 Query OK, 0 rows affected (0.15 sec)
 Records: 0  Duplicates: 0  Warnings: 0

 }}}

 The results speak for themselves :


 {{{
 mysql> SELECT    u.ID,    count(post_author) as posts  FROM    wp_posts as
 p    LEFT JOIN wp_users as u ON p.post_author = u.ID  WHERE
 p.post_status = 'publish'    AND u.ID != ''    AND p.post_type = 'post'
 GROUP BY    p.post_author  HAVING    COUNT(post_author) > 1 ;
 +------+-------+
 | ID   | posts |
 +------+-------+
 |    3 |  2739 |
 |    8 |     8 |
 |   10 |   115 |
 |   14 |    10 |
 |   34 |  1575 |
 |   40 |  3600 |
 |   41 |    38 |
 |   42 |    19 |
 |   45 |     4 |
 |   47 |   217 |
 |   48 |   129 |
 +------+-------+
 11 rows in set (0.01 sec)
 }}}

 the explain shows the benefits :


 {{{


 mysql> explain SELECT    u.ID,    count(post_author) as posts  FROM
 wp_posts as p    LEFT JOIN wp_users as u ON p.post_author = u.ID  WHERE
 p.post_status = 'publish'    AND u.ID != ''    AND p.post_type = 'post'
 GROUP BY    p.post_author  HAVING    COUNT(post_author) > 1 ;
 +----+-------------+-------+-------+-------------------------------------------------------+--------------------------+---------+---------------------------------+------+-----------------------------------------------------------+
 | id | select_type | table | type  | possible_keys
 | key                      | key_len | ref                             |
 rows | Extra                                                     |
 +----+-------------+-------+-------+-------------------------------------------------------+--------------------------+---------+---------------------------------+------+-----------------------------------------------------------+
 |  1 | SIMPLE      | u     | index | PRIMARY
 | user_nicename            | 152     | NULL                            |
 15 | Using where; Using index; Using temporary; Using filesort |
 |  1 | SIMPLE      | p     | ref   |
 type_status_date,post_author,imran_status_type_author |
 imran_status_type_author | 132     | const,const,fakefake_blogg.u.ID |
 198 | Using where; Using index                                  |
 +----+-------------+-------+-------+-------------------------------------------------------+--------------------------+---------+---------------------------------+------+-----------------------------------------------------------+
 2 rows in set (0.00 sec)

 }}}

--
Ticket URL: <https://core.trac.wordpress.org/ticket/27324>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform