wordpress query custom fields and category

Question

I have a query that creates a table view and then another that queries the view. The results are extremely slow. Here is the code:

create or replace view $view_table_name as select * from wp_2_postmeta where post_id IN (                           
                        select ID FROM wp_2_posts wposts 
                            LEFT JOIN wp_2_term_relationships ON (wposts.ID = wp_2_term_relationships.object_id) 
                            LEFT JOIN wp_2_term_taxonomy ON (wp_2_term_relationships.term_taxonomy_id = wp_2_term_taxonomy.term_taxonomy_id) 
                        WHERE wp_2_term_taxonomy.taxonomy = 'category'
                            AND wp_2_term_taxonomy.parent = $cat || wp_2_term_taxonomy.term_id = $cat                                                                                                                       
                            AND wposts.post_status = 'publish' 
                            AND wposts.post_type = 'post')

The $values have been put it in for this example that queries the view table for the results.

select distinct(ID)
FROM $view_table_name wposts
    LEFT JOIN wp_2_postmeta wpostmeta
        ON wposts.ID = wpostmeta.post_id
WHERE post_status = 'publish'
    AND ID NOT IN (SELECT post_id
                   FROM wp_2_postmeta
                   WHERE meta_key = '$var' && meta_value = '$value1')
    AND ID NOT IN (SELECT post_id
                   FROM wp_2_postmeta
                   WHERE meta_key = '$var' && meta_value = '$value2')
    AND ID NOT IN (SELECT post_id
                   FROM wp_2_postmeta
                   WHERE meta_key = '$var' && meta_value = '$value3')
    AND postmeta.meta_key = 'pd_form'
ORDER BY CASE wpostmeta.meta_value
             WHEN '$value5' THEN 1
             WHEN '$value6' THEN 2
             WHEN '$value7' THEN 3
             WHEN '$value8' THEN 4
             WHEN '$value9' THEN 5
             WHEN '$value10' THEN 6
             WHEN '$value11' THEN 7
             WHEN '$value11' THEN 8
         END;

Answer 1

The main problem here is a subquery in IN condition. Instead executing the subquery and then checking in the outer table for correspondences, MySQL is known to transform the query into a correlated subquery which is executed for each row in the outer table.

The usual solution is to get rid of the subquery in the IN in favour of a JOIN.

Another problem is that you use OUTER JOIN instead of inner JOIN though you do not actually need it (MySQL is usually smart enough to optimize it when it is trivial, but anyway you should express your intention more clearly).

And one more thing. Both queries seem to be dynamically generated. Beside optimizing the query itself, one should think how not to break the calling code. That may be tricky though.

Optimizing wordpress is always an interesting challenge.