1

I've gathered that I may be able to solve my slow query problems on a WordPress site with over 200k posts by using indexes. Hoping someone could help explain some.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (7,8,14,15,16,18,20,21,30,34,39,54,62,64,86,89,143,146,152,170,213,233,236,238,241,244,246,251,256,263,269,335) ) )
AND ( wp_postmeta.meta_key = 'post_views_count' )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_postmeta.meta_value+0 DESC
LIMIT 0, 20

This is the slow query in question, its calling the post, I vaguely understand what its doing to be honest. I understand what the various tables mean but no idea how I could speed it up.

Here is the explain of said query;

    +------+--------------+------------------------+--------+--------------------------+------------------+---------+-------------------------------------+--------+----------------------------------------------+
| id   | select_type  | table                  | type   | possible_keys            | key              | key_len | ref                                 | rows   | Extra                                        |
+------+--------------+------------------------+--------+--------------------------+------------------+---------+-------------------------------------+--------+----------------------------------------------+
|    1 | PRIMARY      | wp_postmeta           | ref    | post_id,meta_key         | meta_key         | 767     | const                               | 441856 | Using where; Using temporary; Using filesort |
|    1 | PRIMARY      | wp_posts              | eq_ref | PRIMARY,type_status_date | PRIMARY          | 8       | wpdb.wp_postmeta.post_id |      1 | Using where                                  |
|    2 | MATERIALIZED | wp_term_relationships | range  | PRIMARY,term_taxonomy_id | term_taxonomy_id | 8       | NULL                                | 658445 | Using where; Using index                     |
+------+--------------+------------------------+--------+--------------------------+------------------+---------+-------------------------------------+--------+----------------------------------------------+

Obviously it's accessing a huge amount of tables. Is there any way I can create indexes to vastly lower the rows being read?

The previous and the following are seperate but both get_posts() queries.

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (7,8,14,15,16,18,20,21,30,34,39,54,62,64,86,89,143,146,152,170,213,233,236,238,241,244,246,251,256,263,269,335) ) )
AND wp_posts.post_type = 'post'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_date DESC
LIMIT 0, 20

explain;

+------+--------------+------------------------+-------+--------------------------+------------------+---------+------+--------+------------------------------------------+
| id   | select_type  | table                  | type  | possible_keys            | key              | key_len | ref  | rows   | Extra                                    |
+------+--------------+------------------------+-------+--------------------------+------------------+---------+------+--------+------------------------------------------+
|    1 | PRIMARY      | wp_posts              | index | type_status_date         | type_status_date | 180     | NULL | 234322 | Using where; Using index; Using filesort |
|    2 | MATERIALIZED | wp_term_relationships | range | PRIMARY,term_taxonomy_id | term_taxonomy_id | 8       | NULL | 658493 | Using where; Using index                 |
+------+--------------+------------------------+-------+--------------------------+------------------+---------+------+--------+------------------------------------------+

This is from adjacent_post() which is a theme specific query that only moves to posts in the same category (previous/next) SELECT p.ID FROM wp_posts AS p INNER JOIN wp_term_relationships AS tr ON p.ID = tr.object_id INNER JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE p.post_date < '2016-11-17 09:22:45' AND p.post_type = 'post' AND tt.taxonomy = 'category' AND tt.term_id IN (54) AND ( p.post_status = 'publish' OR p.post_status = 'private' ) ORDER BY p.post_date DESC LIMIT 1

explain;

+------+-------------+-------+--------+-----------------------------------+------------------+---------+-----------------------------+-------+----------------------------------------------+
| id   | select_type | table | type   | possible_keys                     | key              | key_len | ref                         | rows  | Extra                                        |
+------+-------------+-------+--------+-----------------------------------+------------------+---------+-----------------------------+-------+----------------------------------------------+
|    1 | SIMPLE      | tt    | const  | PRIMARY,term_id_taxonomy,taxonomy | term_id_taxonomy | 138     | const,const                 |     1 | Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | tr    | ref    | PRIMARY,term_taxonomy_id          | term_taxonomy_id | 8       | const                       | 67134 | Using index                                  |
|    1 | SIMPLE      | p     | eq_ref | PRIMARY,type_status_date          | PRIMARY          | 8       | wpdb.tr.object_id |       1 | Using where                                  |
+------+-------------+-------+--------+-----------------------------------+------------------+---------+-----------------------------+-------+----------------------------------------------+
  • Anything using filesort is really bad. The problem with your first query is that the postmeta table is not made for conditional queries, and the subquery is terribly slow. An index might help, but it had to be rebuilt on every write operation, which happens surprisingly often, especially when you are storing a view count here. That's the worst possible place for that information, btw. For the adjacent posts link see get_adjacent_post alternative on a very large db. – fuxia Nov 18 '16 at 0:27
  • I've gotten rid of the views storing, it was a poorly made plugin. – ChaosTheoryV2X Nov 18 '16 at 1:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.