I've several boxes inside a Wordpress site that have to exhibit an array of posts which fit in with certain groups As Well As don't fit in with several others. Only using native wordpress tools it makes sense a question such as this:
SELECT SQL_CALC_FOUND_ROWS wp_posts.* 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) WHERE 1=1 AND wp_term_taxonomy.taxonomy = 'category' AND wp_term_taxonomy.term_id IN ('1', '49') AND wp_posts.ID NOT IN ( SELECT tr.object_id FROM wp_term_relationships AS tr INNER JOIN wp_term_taxonomy AS tt ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy = 'category' AND tt.term_id IN ('3394', '49', '794', '183') ) 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, 5;
this is very costly and eventually ends up within the slow queries log. So I'll happily drop the wordpress way and make the query by hand. The very first factor is will be able to eliminate the requirement for the
wp_term_taxonomy table (I'm able to make use of the term_taxonomy_id rather than term_id, I never understood why wordpress needs both) however i have the
NOT IT ( SUBQUERY ) problem because of the groups to become excluded.
So thinking about that I am liberated to do anything whatsoever (like create other "service" tables when they may help), what's the easiest method to accelerate this search under these conditions?
I do not see the requirement for the subquery, because the tables it's using happen to be inside your primary query! So exluding stuff ought to be rather simple of adding more towards the WHERE claims.
Can't you edit your line
AND wp_term_taxonomy.term_id IN ('1', '49')
AND wp_term_taxonomy.term_id IN ('1', '49') AND wp_term_taxonomy.term_id NOT IN ('3394', '49', '794', '183')
..after which getting rid of the subquery completely?