I'm searching for outside assistance optimizing a MySQL query I authored to obtain related posts from the WordPress database. Essentially, the query is made to find related posts by counting up the number of tags the posts share having a given subject publish. Whether it is important, the tags I am searching through aren't WordPress's built-in
'post-tag' but a custom flat taxonomy which i produced for that site.
The query appears to operate fine, but I am not really a database expert, and I am worried that generate an income possess the JOINs setup is a performance problem once I've got a large amount of posts sort through. I possibly could be wrong though. Below is really a typical query for example. Anybody have ideas around the performance of the query? Or, maybe there is a simpler way to do this using the WordPress API?
Of course, any assistance is greatly appreciated.
SELECT wp_posts.post_status, wp_posts.post_type, wp_posts.ID, wp_posts.post_title, wp_term_relationships.term_taxonomy_id, wp_term_taxonomy.term_id, COUNT( 1 ) AS `common_count` FROM `wp_term_relationships` INNER JOIN `wp_posts` 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 ( wp_term_taxonomy.term_id = 15 OR wp_term_taxonomy.term_id = 19 OR wp_term_taxonomy.term_id = 20 ) AND ( wp_posts.ID != 28 ) AND ( wp_posts.post_status = 'publish' ) AND ( wp_posts.post_type = 'my-custom-post-type' ) GROUP BY wp_posts.ID ORDER BY `common_count` DESC LIMIT 2