CODEWITHSUNDEEP
mysqlwordpressquery-optimization
Incognito
Incognito

Reputation: 1953

how would I optimize this wordpress query?

I'm trying to figure out the optimal combination of indexes for the query below. Right now its Using temporary; Using filesort and killing my vps. Queries take from 6-10 seconds.

SELECT SQL_CALC_FOUND_ROWS 
  wp_posts.ID     
FROM 
  wp_posts
  INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)
  INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id)
  INNER JOIN wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id) 
WHERE
  1=1
  AND wp_term_taxonomy.taxonomy = 'post_tag' 
  AND wp_terms.slug IN ('pie') 
  AND wp_posts.post_type = 'post'
  AND (wp_posts.post_status = 'publish') 
GROUP BY 
  wp_posts.ID     
ORDER BY 
  wp_posts.post_date DESC 
LIMIT 0, 20

Any suggestions?

Upvotes: 0

Views: 561

Answers (1)

Tomalak
Tomalak

Reputation: 338188

How does this compare, performance-wise?

SELECT
  p.ID
FROM 
  wp_posts AS p
WHERE
  1=1
  AND p.post_type = 'post'
  AND p.post_status = 'publish'
  AND EXISTS (
    SELECT 1 
      FROM wp_term_relationships AS r 
           INNER JOIN wp_term_taxonomy AS t ON 
             r.term_taxonomy_id = t.term_taxonomy_id AND t.taxonomy = 'post_tag'
           INNER JOIN wp_terms AS m ON 
             m.term_id = t.term_id AND m.slug IN ('pie') 
     WHERE r.object_id = wp_posts.ID 
  )
ORDER BY 
  p.post_date DESC 
LIMIT 0, 20

Indexes should be on all the primary and foreign keys, on wp_terms.slug, on wp_term_taxonomy.taxonomy and a composite one over wp_posts.post_status, post_type, post_date.

Upvotes: 1

Related Questions