Very slow query

Question

This query works, but it takes upwards of 20 seconds to run on a Quad Core dedicated server... How can I optimize this?

$slides=get_posts(
    array('post_type' => 'any', 'numberposts' => 5, 'orderby' => 'date', 'order' => 'desc', "tax_query" =>
        array(
                "relation" => "OR",
                 array(
                    "field" => "id",
                    "terms" => array(36),
                    "taxonomy" => "category",
                    "operator" => "IN"
                ),
                array(
                    "field" => "id",
                    "terms" => array(36),
                    "taxonomy" => "music-categories",
                    "operator" => "IN"
                ),
                array(
                    "field" => "id",
                    "terms" => array(36),
                    "taxonomy" => "video-categories",
                    "operator" => "IN"
                ),
                array(
                    "field" => "id",
                    "terms" => array(36),
                    "taxonomy" => "mixtape-categories",
                    "operator" => "IN"
            )
            )
        )
);

Answer 1

To check if the query itself is slow or anything else slows it down, try running your query directly in the database, and have a look at how long it takes there.

<?php echo $GLOBALS['wp_query']->request; ?>

This shows you the latest Query that WordPress ran in your database.

If the query is really slow in the database, try caching the results, running this specific quer only once every hour or so. I usually do this with menus - but it works with anything. Using transients to save the output HTML.

I wrote about this technique here.

If you need different output for different taxonomies, try adding the TermID in the name of the transient.

You could delete the transients either on 'update_post' or let them expire after a certain time.

Answer 2

1. Use explicit post types, i.e. 'post_type' => array('post','music'...). Right now your query check revisions and attachments as well and they are probably half of the post in your DB.

2. From your code it looks like you should know in advance which taxonomy is relevant to your query (it is unlikely that a term number 36 has relevant meaning in all 4 taxonomies). You should query for only the relevant taxonomies.

3. If you just need the 5 most recent published posts, then maybe you should calculate it on the fly when new posts are added, avoiding the use of the query.

   add_action('publich_post','my77483_change_cache');
   function my77483_change_cache($post_id,$post) {
     if (not meeting criteria)
       return;
     $chached = get_option('my77483_query_cache');
     if (count($cached) == 5) 
       array_shift($cached); // remove the oldest post. 
     $cached[] = $post_id;
     update_option('my77483_query_cache',$cached);
   }
   

This way your query becomes

$chached = get_option('my77483_query_cache');
$posts = get_posts('post__in' => $cached);

The only problem is what to do in delete, then maybe you should run you original query, but that should be a very rare event.