[Solved] Speed up JOIN with subquery
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

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?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Subqueries (query line: 13): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  2. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  3. Mixed Order By Directions Prevents Index Use (query line: 33): The database will not use a sorting index (if exists) in cases where the query mixes ASC (the default if not specified) and DESC order. To avoid filesort, you may consider using the same order type for all columns. Another option that will allow you to switch one direction to another is to create a new reversed "sort" column (max_sort - sort) and index it instead.
Optimal indexes for this query:
ALTER TABLE `zwzt_wp_posts` ADD INDEX `zwzt_posts_idx_post_type_post_statu_post_autho` (`post_type`,`post_status`,`post_author`);
ALTER TABLE `zwzt_wp_users` ADD INDEX `zwzt_users_idx_id` (`id`);
The optimized query:
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

Related Articles



* original question posted on StackOverflow here.