3

I have a fairly large database - 113,299 rows in wp_posts and 216,0649 rows in wp_postmeta.

There is one custom query that I run after a post is added or edited, which appears in MySQL's slow-log file every time it runs -- and seems to take way too many seconds (between 17 to 78 seconds, in fact).

This is what it looks like in query_posts:

    $args = array( 
                'meta_query' => array(
                                    array(
                                        'key' => 'article_template',
                                        'value' => 'news',
                                    ),
                                ),
                'posts_per_page' => '30',
                'category__in' => array( 3, 4, 5 ),
                'post_status' => 'publish',
                'no_found_rows' => true,
                'orderby' => 'meta_value',
                'meta_key' => 'article_datetime',
                'order' => 'DESC'
            );
    query_posts( $args );

And this is what it looks like in MySQL's slow-log file:

SELECT   wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) WHERE 1=1  AND ( wp_term_relationships.term_taxonomy_id IN (3,4,5) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.meta_key = 'article_datetime'
AND  (mt1.meta_key = 'article_template' AND CAST(mt1.meta_value AS CHAR) = 'news') ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC LIMIT 0, 30;

This is what EXPLAIN shows for this query:

mysql> explain SELECT   wp_posts.ID FROM wp_posts  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
    -> INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) WHERE 1=1  AND ( wp_term_relationships.term_taxonomy_id IN (3,4,5) ) AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish') AND (wp_postmeta.meta_key = 'article_datetime' AND  (mt1.meta_key = 'article_template' AND CAST(mt1.meta_value AS CHAR) = 'news') ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value DESC LIMIT 0, 30;
+----+-------------+-----------------------+--------+--------------------------+----------+---------+-----------------------------+-------+----------------------------------------------+
| id | select_type | table                 | type   | possible_keys            | key      | key_len | ref                         | rows  | Extra                                        |
+----+-------------+-----------------------+--------+--------------------------+----------+---------+-----------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | wp_postmeta           | ref    | post_id,meta_key         | meta_key | 768     | const                       | 98576 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | wp_posts              | eq_ref | PRIMARY,type_status_date | PRIMARY  | 8       | toi_web.wp_postmeta.post_id |     1 | Using where                                  |
|  1 | SIMPLE      | mt1                   | ref    | post_id,meta_key         | post_id  | 8       | toi_web.wp_postmeta.post_id |    11 | Using where                                  |
|  1 | SIMPLE      | wp_term_relationships | ref    | PRIMARY,term_taxonomy_id | PRIMARY  | 8       | toi_web.mt1.post_id         |     2 | Using where; Using index                     |
+----+-------------+-----------------------+--------+--------------------------+----------+---------+-----------------------------+-------+----------------------------------------------+

So I'm wondering if anyone has a good tip or suggestion how to optimize this and avoid the slow query? Maybe break this into a different query_posts loop? Or first grab the posts' IDs with a simple wpdb->get_results query?

I'm open to any suggestions :)

By the way, we have a very robust MySQL cluster, so server resources is not the issue.

  • 5
    You should never use query_posts, use pre_get_posts or WP_Query instead – Tom J Nowell Oct 29 '14 at 22:53
  • 4
    It is almost certainly the 'orderby' => 'meta_value',. You may find it quicker to sort your posts in PHP. – s_ha_dum Oct 29 '14 at 22:57
  • Apologies for my ignorance, Tom, but does it affect the query speed? Using the exact same $args, WP_Query doesn't generate a different MySQL query than query_posts so therefore the query will still be slow. And why not use query_posts when this is a completely custom query? It's not running inside a loop, and it's not hooking into an existing wordpress query. So why is query_posts forbidden? Thanks for the answer and for your help. – Biranit Goren Oct 29 '14 at 22:58
  • 1
    @RyanLoremIpsum, yes I can - and tried it. But the end result is the same, since it creates the same MySQL. I think the only viable way for me will be to try and avoid using wp_postmeta in the same query - so probably use a simple MySQL query to grab the relevant post IDs and then run them into a WP_Query loop... – Biranit Goren Oct 30 '14 at 0:23
  • 1
    Depending on your server configuration, setting 'cache_results' => false may improve performance. codex.wordpress.org/Class_Reference/WP_Query#Caching_Parameters – iyrin Oct 30 '14 at 0:48
2
  1. Persuming "article_datetime" to be a numeric value, I would suggest that instead of using 'orderby' => 'meta_value' use 'orderby' => 'meta_value_num'
  2. If still query is slow, than remove:

    'orderby' => 'meta_value', 'meta_key' => 'article_datetime', 'order' => 'DESC'

and instead sort the result in php.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.