WordPress.org

Make WordPress Core

Opened 7 years ago

Closed 7 years ago

#27324 closed enhancement (invalid)

increase performance of table joins on wp_users and wp_posts

Reported by: NAPPA Owned by:
Milestone: Priority: normal
Severity: normal Version: 3.8.1
Component: Database Keywords:
Focuses: performance Cc:

Description

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)

Attachments (2)

wordpress query optimisation benefit.jpg (203.3 KB) - added by NAPPA 7 years ago.
query benefit
71.diff (626 bytes) - added by NAPPA 7 years ago.
diff of the change required in : schema

Download all attachments as: .zip

Change History (4)

@NAPPA
7 years ago

query benefit

#1 @NAPPA
7 years ago

+ 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
7 years ago

diff of the change required in : schema

#2 @pento
7 years ago

  • Milestone Awaiting Review deleted
  • Resolution set to invalid
  • Status changed from new to closed

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.

Note: See TracTickets for help on using tickets.