CODEWITHSUNDEEP
mysql
Jan Z.
Jan Z.

Reputation: 6943

Speed up JOIN with subquery

In a wordpress database, I want to select all published articles from authors who have written at least five articles, and replace the authors' IDs with their login names. I accomplished that with a selection of all published posts, filtering that two times with a JOIN statement, one of them joining the result set of a subquery to get those authors with more than five articles.

Here we go with the code:

SELECT posts.post_author, users.user_login, posts.post_content, posts.post_date
FROM zwzt_wp.zwzt_wp_posts AS posts 

INNER JOIN zwzt_wp.zwzt_wp_users AS users
ON posts.post_author=users.id

INNER JOIN (
    SELECT posts1.post_author, COUNT(*) AS count
    FROM zwzt_wp.zwzt_wp_posts AS posts1 
    WHERE posts1.post_type='post' AND posts1.post_status='publish'
    GROUP BY posts1.post_author
    HAVING count > 5
    ORDER BY posts1.post_author
) as regular_users
ON posts.post_author=regular_users.post_author

WHERE posts.post_type='post' AND posts.post_status='publish'
ORDER BY posts.post_author, posts.post_date DESC;

To me this looks kinda bloated and time intensive to run, especially since I run two select queries on posts. Is there any way to accomplish this in a more efficient way?

Upvotes: 0

Views: 151

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I don't think there is much you can do to simplify the query. But, there are some things you can do:

  • Remove the ORDER BY from the subquery.
  • Create an index on zwzt_wp_posts(post_type, post_status, post_author).
  • Make sure you have indexes on users(id).
  • Make sure you have an index on zwzt_wp_posts(post_author) as well.

If this type of information is regularly used in queries, you might want to set up triggers to maintain the counts at the user level.

Upvotes: 2

Related Questions