posts_join_filter adding duplicate LEFT JOINS causing very slow is_author queries #417
Description
@trepmal @mjangda This issue appears to have been introduced in this commit: e1bda76
Enable Coauthors Plus and view the main query on an author archive page to see the resulting SQL statement.
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_term_relationships AS tr1
ON (wp_posts.ID = tr1.object_id)
LEFT JOIN wp_term_taxonomy
ON ( tr1.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id )
WHERE 1=1
AND ((wp_posts.post_author = 83872
OR (wp_term_taxonomy.taxonomy = 'author'
AND wp_term_taxonomy.term_id = '12475')))
AND wp_posts.post_type IN ('post', 'sponsor_post')
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
HAVING MAX( IF ( wp_term_taxonomy.taxonomy = 'author', IF ( wp_term_taxonomy.term_id = '12475',2,1 ),0 ) ) <> 1
ORDER BY wp_posts.post_date DESC
LIMIT 0, 30
Notice that the wp_term_relationships LEFT JOIN with wp_posts is repeated twice. Once with an alias and once without an alias. This duplicate join statement does not cause the SQL query to fail, but it does make the query extremely slow. Without the duplicate join, my is_author main query takes 0.1s. When the duplicate join is there, the same query takes 1.0-1.5 secs to return a result.
I'm not entirely sure why the aliased query was needed, but the fix might be as simple as reverting that commit.
If for some reason the aliased query needs to be there, the code needs to replace the unaliased query, not add to it, which is what it does now.