Super slow queries from author archive pages killing my database

Question

I'm wondering if you can help me figure something out with these queries from author archive pages that are killing my database server. These queries are taking 7-15 seconds to run!

Here's an example:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID 
FROM wp_posts 
   LEFT JOIN wp_term_relationships ON (
      wp_posts.ID = wp_term_relationships.object_id
   ) 
   LEFT JOIN wp_term_taxonomy ON ( 
      wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id 
   ) 
WHERE 
   1=1 
   AND (
     ( 
       wp_posts.post_author = 2336 
       OR (
         wp_term_taxonomy.taxonomy = 'author' 
         AND wp_term_taxonomy.term_id = '12954'
       )
     )
   ) 
   AND wp_posts.post_type = 'post' 
   AND (
      wp_posts.post_status = 'publish' 
      OR wp_posts.post_status = 'private'
   ) 
   GROUP BY wp_posts.ID 
   HAVING MAX( 
     IF( 
        wp_term_taxonomy.taxonomy = 'author', 
        IF ( wp_term_taxonomy.term_id = '12954',2,1 ),
        0 
     ) 
   ) <> 1 
   ORDER BY wp_posts.post_date DESC 
   LIMIT 0, 10

From rhrealitycheck.org/author/name-edited/ in /nas/wp/www/cluster-1516/rhrealitycheck/index.php:17

require('wp-blog-header.php'), wp, WP->main, WP->query_posts, WP_Query->query, WP_Query->get_posts (9,297.1ms)

Why is this query so complex just to pull the latest 10 posts from a particular author?

Is there something I can do to override the query, like a pre_get_posts filter in functions.php? If so how would I start that filter and target it just to author archive pages?

Answer 1

If memory serves, the problem is because you are doing a very large query and then running multiple checks across all of them with your max statement.

You might have much better luck breaking it out into two queries so that you can keep the iterative processing to a minimum.

For example, you might:

• Query the posts that have your wanted author
• Query the posts that have the appropriate author term attached
• Combine the results and display them manually.

I don't have time at the moment to try and come up with a cleaner query ... but that query looks a bit processing intensive ... and sometimes it really is faster to do two simple queries.