Mysql – Optimize slow WordPress Query

MySQLmysql-5.7

I have one query that takes several seconds to execute. I'm using version 5.7.19. 

SELECT 
    SQL_CALC_FOUND_ROWS wp_posts.*, 
    SUM( IFNULL( pvc.count, 0 ) ) AS post_views 
FROM 
    wp_posts 
    LEFT JOIN wp_term_relationships 
        ON (wp_posts.ID = wp_term_relationships.object_id) 
    LEFT JOIN wp_post_views pvc 
        ON pvc.id = wp_posts.ID 
           AND pvc.type = 0 
           AND STR_TO_DATE(pvc.period, '%Y%m%d') BETWEEN NOW() - INTERVAL 30 DAY AND NOW() 
WHERE 
    1=1 
    AND (wp_term_relationships.term_taxonomy_id IN (2) ) 
    AND wp_posts.post_type = 'post' 
    AND (wp_posts.post_status = 'publish') 
GROUP BY 
    wp_posts.ID 
ORDER BY 
    post_views DESC,  wp_posts.ID DESC 
LIMIT 
    0, 16 ;

The stripped down result:

+-------+------------+
| ID    | post_views |
+-------+------------+
| 25324 |       8037 |
| 30866 |       6442 |
| 27720 |       5974 |
| 30161 |       5354 |
| 31207 |       5028 |
| 11901 |       4851 |
| 30739 |       4271 |
|  3331 |       3723 |
|   436 |       3685 |
| 14980 |       3250 |
| 31297 |       3087 |
|  3745 |       3019 |
| 29858 |       2945 |
|  2503 |       2882 |
|   416 |       2880 |
| 23773 |       2577 |
+-------+------------+

Result of the EXPLAIN query

+----+-------------+-----------------------+------------+--------+---------------------------------------------------------------+----------------------+---------+---------------------------------------------------+------+----------+-----------------------------------------------------------+
| id | select_type | table                 | partitions | type   | possible_keys                                                 | key                  | key_len | ref                                               | rows | filtered | Extra                                                     |
+----+-------------+-----------------------+------------+--------+---------------------------------------------------------------+----------------------+---------+---------------------------------------------------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | wp_term_relationships | NULL       | ref    | PRIMARY,term_taxonomy_id                                      | term_taxonomy_id     | 8       | const                                             | 2190 |   100.00 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | wp_posts              | NULL       | eq_ref | PRIMARY,post_name,type_status_date,post_parent,post_author,ID | PRIMARY              | 8       | wpHdStreams.wp_term_relationships.object_id       |    1 |     7.80 | Using where                                               |
|  1 | SIMPLE      | pvc                   | NULL       | ref    | PRIMARY,id_type_period_count,type_period_count,id             | id_type_period_count | 9       | wpHdStreams.wp_term_relationships.object_id,const |   16 |   100.00 | Using where; Using index                                  |
+----+-------------+-----------------------+------------+--------+---------------------------------------------------------------+----------------------+---------+---------------------------------------------------+------+----------+-----------------------------------------------------------+

How can I optimize the query? Currently it took at least 2.5 seconds to execute it.

EDIT:

Here are the three tables:
wp_posts

| wp_posts | CREATE TABLE `wp_posts` (
`ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
`post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
`post_title` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
`post_excerpt` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
`post_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'publish',
`comment_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
`ping_status` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'open',
`post_password` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`post_name` varchar(200) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`to_ping` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
`pinged` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
`post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`post_content_filtered` longtext COLLATE utf8mb4_unicode_520_ci NOT NULL,
`post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
`guid` varchar(255) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`menu_order` int(11) NOT NULL DEFAULT '0',
`post_type` varchar(20) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT 'post',
`post_mime_type` varchar(100) COLLATE utf8mb4_unicode_520_ci NOT NULL DEFAULT '',
`comment_count` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `post_name` (`post_name`(191)),
KEY `type_status_date` (`post_type`,`post_status`,`post_date`,`ID`),
KEY `post_parent` (`post_parent`),
KEY `post_author` (`post_author`),
KEY `ID` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=33551 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci |

wp_term_relationships 

| wp_term_relationships | CREATE TABLE `wp_term_relationships` (
`object_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`term_taxonomy_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`term_order` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`object_id`,`term_taxonomy_id`),
KEY `term_taxonomy_id` (`term_taxonomy_id`),
KEY `object_id` (`object_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci |

wp_post_views

| wp_post_views | CREATE TABLE `wp_post_views` (
  `id` bigint(20) unsigned NOT NULL,
  `type` tinyint(1) unsigned NOT NULL,
  `period` varchar(8) COLLATE utf8mb4_unicode_520_ci NOT NULL,
  `count` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`type`,`period`,`id`),
UNIQUE KEY `id_type_period_count` (`id`,`type`,`period`,`count`) USING BTREE,
KEY `type_period_count` (`type`,`period`,`count`) USING BTREE,
KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci |

type indicates the type of the value:

  1. 0 = Year with month and day (Ymd)
  2. 1 = Year with (i think it was) week
  3. 2 = Year with month (Ym)
  4. 3 = Year (Y)
  5. 4 = total

It's this plugin post views counter

Best Answer

Without SHOW CREATE TABLE for each table, I can only guess...

If wp_term_relationships is a many:many mapping table, then follow the schema tips seen in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

If any of those tables is a variant of wp_postmeta, then see the tips in http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta

Also,... Check the math. Often, this pattern comes up with inflated aggregates:

SELECT SUM(...)
    FROM x JOIN y ...
    GROUP BY ...

I call it the "inflate-deflate syndrome. After seeing the schema, I may have more comments. If we can get rid of it then we can get rid of the GROUP BY, which is currently preventing any optimization of the ORDER BY or LIMIT.

The gist of the 'fix' will be to turn post_views into a subquery that has all the JOINing in it. That gets rid of the regular JOIN and the GROUP BY. Once you have done all that, do the IFNULL outside the SUM.

STR_TO_DATE(pvc.period, '%Y%m%d') is hiding a potentially indexable column inside a function. If period is a DATE datatype, then that is identical pvc.period. Even if it is a DATETIME or TIMESTAMP, it should be fine in this context. If it VARCHAR, then shame on you (or on WP?).

More (based on schema)

For pvc, this will cut out the redundant indexes: Replace the 4 you have with these 2. (It will not inpact the current query.)

PRIMARY KEY(id, type, period),
INDEX(type, period, count, id)

See my link for avoiding the 191. (Again, won't impact the current query, but might greatly help others.)

If you can't change period to a DATE (and it looks like you cannot), then the goal is to keep it as a string and aim for

pvc.period BETWEEN CONCAT('', (NOW() - INTERVAL 30 DAY))
               AND CONCAT('',  NOW())

(I don't know if the bogus CONCATs are necessary to change those dates into strings.) Notice how the indexed period is standing alone, not hidden inside a function call.

Be sure to do likewise for the other "types".

Keep in mind that WP and all its plugins seem to be designed for "small" databases. You have come to me to figure out how to use MySQL to improve performance and scalability, that they have yet to consider.

I think that LEFT JOIN should be changed to simply JOIN. After all, if the 'right' table is missing, you want 0 in the SUM, so why bother creating NULLs to have to turn into 0.

Don't use SQL_CALC_FOUND_ROWS unless it is important. It involves doing virtually all of the query as if the LIMIT were absent. (And, again, the return value may be inflated.)

This might come closer:

SELECT  SQL_CALC_FOUND_ROWS  wp_posts.*,
        IFNULL( (
                SELECT  SUM(pvc.count)
                    FROM  wp_post_views AS pvc  ON pvc.id = p.ID
                    WHERE  pvc.type = 0
                      AND  pvc.period BETWEEN ... AND ... ),
         0 ) AS post_views
    WHERE  p.post_type = 'post'
      AND  p.post_status = 'publish'
    ORDER BY  post_views DESC, p.ID DESC
    LIMIT  0, 16

I got rid of wp_term_relationships since it is unused. Note: the LEFT says that it does not matter whether there is a matching row or not.

Related Question