Improve or optimize a very slow query

Question

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.

Answer 1

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.