I have written SQL for a query that pulls the most used taxonomy terms for selected taxonomies that are published, do not have a parent post are not in a comma separated list over the past two weeks — grouped by the term itself sorted by most posts descending.
The issue I am running into is that it is running very slowly and was wondering if you guys had any suggestions for either converting it to wp_query arguments or the right indexes to put on the tables. Following the optimizers path it is running through 4630 rows on wp_term_taxonomy 1 row on wp_terms, 9 rows on wp_term_relationships and 1 row on wp_posts
The query I wrote is basically:
# ** NOTE THIS IS NOT THE EXACT SQL, WRITTEN TO BETTER ARTICULATE MY QUESTION
SELECT wp_terms.name, wp_terms.slug, wp_term_taxonomy.taxonomy,count(1)
WHERE wp.term_taxonomy_id in ('$comma', '$separated', '$taxonomies') AND
wp_posts.post_status = 'publish' AND wp_posts.post_parent = 0 AND
wp_posts.post_date > '$two_weeks_ago' AND wp_terms.term_id NOT IN
($list, $of, $ids, $comma, $separated) group by wp_terms.term_id
order by total_posts desc limit $limit_variable
Appreciate any help.
