increase performance of table joins on wp_users and wp_posts

[NAPPA]

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)

[NAPPA]

query benefit

[NAPPA]

+ added image showing the change, and the benefit there after. The tail end of the image showing the throughput + query time of the change.

[NAPPA]

diff of the change required in : schema

[pento]

Thanks for the bug report!

This query seems to be from an old snippet of code that's been floating around for years, it's not in core, or any plugin I could find.

As such, we won't add a new index to core for it - of course you're welcome to make the index yourself, future WordPress upgrades won't touch it.