How do I optimize this query?

Question

SELECT DISTINCT wposts.* 
    FROM wp_2_posts wposts, wp_2_postmeta wpostmeta, wp_2_postmeta wpostmeta1, wp_2_term_taxonomy, wp_2_terms, wp_2_term_relationships
    WHERE wposts.ID = wpostmeta.post_id
    AND wp_2_terms.term_id = '8'
    AND wp_2_term_taxonomy.term_id = wp_2_terms.term_id
    AND wp_2_term_taxonomy.term_taxonomy_id = wp_2_term_relationships.term_taxonomy_id
    AND wp_2_term_relationships.object_id = wposts.ID
    AND wpostmeta.meta_key = 'validity'
    AND wpostmeta.meta_value > '".$logic_date."'    
AND wpostmeta1.meta_key != 'permanent'  
    AND wposts.post_status = 'publish' 
    AND wposts.post_type = 'post'
    ORDER BY wposts.post_date DESC

Good advice so far, heres the 'evolved' query which still does not use the second meta_key != 'permanent_listing'

SELECT wposts.* FROM wp_2_posts wposts LEFT JOIN wp_2_postmeta wpostmeta ON wposts.ID = wpostmeta.post_id LEFT JOIN wp_2_term_relationships wrelationships ON wrelationships.object_id = wposts.ID LEFT JOIN wp_2_term_taxonomy wtaxonomy ON wtaxonomy.term_taxonomy_id = wrelationships.term_taxonomy_id LEFT JOIN wp_2_terms wterms ON wtaxonomy.term_id = wterms.term_id WHERE wterms.term_id = '--category id here--' AND wpostmeta.meta_key = 'wpx_validity' AND wpostmeta.meta_value > '--todays date here--' AND wpostmeta.meta_key != 'permanent_listing' AND wposts.post_status = 'publish' AND wposts.post_type = 'post' ORDER BY wposts.post_date DESC

LEFT JOIN and INNER JOIN seem to do the same thing. the following indexes exist: wp_2_terms.term_id, wp_term_taxonomy.term_taxonomy_id, wp_2_term_relationships.object_id wp_2_postmeta has a meta_id index, and fields: post_id, meta_key, meta_value.

this query is running on WPMU.

Answer 1

At first I recommend that you use the INNER JOIN syntax to join the tables:

SELECT table1.c1, table2.c2 FROM table1
  INNER JOIN table2 ON table1.ck = table2.ck

Also you should avoid using the * operator and carefully select the columns you need. DISTINCT also slows down your query. Do you really need it?

Answer 2

if the query is too slow, there are different solutions.

First of all try to create some indexes,

then try to reduce the joins filtering the some of the data with php.

if it is still slow, probably the Database schema is wrong and should be changed.

Answer 3

I'll bet you $5 it is an indexing problem. The first thing I'd do is make sure all the fields you are joining the tables on are indexed.

Answer 4

First, your wpostmeta1 does not join with anything.

Second, you don't need DISTINCT here, you need IN.

Try this:

SELECT  wposts.* 
FROM    wp_2_posts wposts
JOIN    wp_2_postmeta wpostmeta
ON      wpostmeta.post_id = wposts.ID
        AND wpostmeta.meta_key = 'validity'
        AND wpostmeta.meta_value > '".$logic_date."'
JOIN    wp_2_postmeta wpostmeta1,
ON      wpostmeta1.post_id = wposts.ID
        AND wpostmeta1.meta_key != 'permanent'  
WHERE   wposts.ID IN
        (
        SELECT  wp_2_term_relationships.object_id
        FROM    wp_2_term_relationships
        JOIN    wp_2_term_taxonomy
        ON      wp_2_term_taxonomy.term_taxonomy_id = wp_2_term_relationships.term_taxonomy_id
        WHERE   wp_2_term_taxonomy.term_id = '8'
        )
        AND wposts.post_status = 'publish' 
        AND wposts.post_type = 'post'
ORDER BYc
        wposts.post_date DESC

Create the following indexes (or make sure they exist):

wp_2_term_relationships (object_id, term_taxonomy_id)
wp_2_term_taxonomy (term_taxonomy_id, term_id)
wp_2_posts (post_status, post_type, post_date, id)