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')

to

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?