[Solved] Extremely slow query
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Extremely slow query

Below is a query which takes 30+ seconds to run. Based on similar queries I have running, I can't see where the hold up is here. My only thought is joining the job user id to job_applicants user id, but they need to be mapped.

SELECT DISTINCT u.user_id, u.first_name, u.last_name FROM users u
LEFT OUTER JOIN employee_access ea ON ea.user_id = u.user_id
LEFT OUTER JOIN confirmation c ON c.user_id = u.user_id
LEFT OUTER JOIN job_applicants a ON a.user_id = u.user_id
LEFT OUTER JOIN job j ON j.job_id = a.job_id
WHERE ea.access_id = 4 OR c.access_id = 4 OR (a.process_level = 0 AND j.access_id = 4) 
ORDER BY u.last_name asc

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 OR Conditions By Using UNION (modified query below): In mosts cases, filtering using the OR operator cannot be applied using indexes. A more optimized alternative will be to split the query to two parts combined with a UNION clause, while each query holds one part of the original OR condition.
  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. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `job_applicants`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.
  4. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `job`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.
  5. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `confirmation`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.
  6. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `employee_access`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.
  7. Remove Redundant Left Joins (modified query below): Redundant LEFT JOINs (e.g. `employee_access`) were detected in the query. Removing them will result in a performance improvement. In some cases, JOINs become redundant after an optimization is applied, such as when converting OR conditions to a UNION clause.
  8. Remove Redundant Left Joins (modified query below): Redundant LEFT JOINs (e.g. `confirmation`) were detected in the query. Removing them will result in a performance improvement. In some cases, JOINs become redundant after an optimization is applied, such as when converting OR conditions to a UNION clause.
  9. Remove Redundant Left Joins (modified query below): Redundant LEFT JOINs (e.g. `job`) were detected in the query. Removing them will result in a performance improvement. In some cases, JOINs become redundant after an optimization is applied, such as when converting OR conditions to a UNION clause.
  10. Remove Redundant Left Joins (modified query below): Redundant LEFT JOINs (e.g. `job_applicants`) were detected in the query. Removing them will result in a performance improvement. In some cases, JOINs become redundant after an optimization is applied, such as when converting OR conditions to a UNION clause.
  11. Replace Join With Exists To Avoid Redundant Grouping (modified query below): When a joined table isn’t used anywhere other than in the WHERE clause, it's equivalent to an EXISTS subquery, which often performs better. In cases where the DISTINCT or GROUP BY clause contains only columns from the Primary key, they can be removed to further improve performance, as after this transformation, they are redundant.
  12. Use UNION ALL instead of UNION (query line: 61): Always use UNION ALL unless you need to eliminate duplicate records. By using UNION ALL, you'll avoid the expensive distinct operation the database applies when using a UNION clause.
Optimal indexes for this query:
ALTER TABLE `confirmation` ADD INDEX `confirmation_idx_access_id` (`access_id`);
ALTER TABLE `employee_access` ADD INDEX `employee_access_idx_access_id` (`access_id`);
ALTER TABLE `job` ADD INDEX `job_idx_access_id_job_id` (`access_id`,`job_id`);
ALTER TABLE `job_applicants` ADD INDEX `job_applicants_idx_process_le_user_id_job_id` (`process_level`,`user_id`,`job_id`);
ALTER TABLE `users` ADD INDEX `users_idx_last_name` (`last_name`);
ALTER TABLE `users` ADD INDEX `users_idx_user_id_last_name` (`user_id`,`last_name`);
The optimized query:
SELECT
        u_user_id,
        u_first_name,
        u_last_name 
    FROM
        ((SELECT
            DISTINCT u.user_id AS u_user_id,
            u.first_name AS u_first_name,
            u.last_name AS u_last_name 
        FROM
            users u 
        WHERE
            (
                1 = 1 
                AND 1 = 1
            ) 
            AND (
                EXISTS (
                    SELECT
                        1 
                    FROM
                        job_applicants a 
                    INNER JOIN
                        job j 
                    WHERE
                        (
                            (
                                (
                                    a.user_id = u.user_id
                                ) 
                                AND (
                                    a.process_level = 0
                                )
                            ) 
                            AND (
                                j.job_id = a.job_id
                            )
                        ) 
                        AND (
                            j.access_id = 4
                        )
                )
            ) 
        ORDER BY
            u.last_name ASC) 
        UNION
        DISTINCT (SELECT
            DISTINCT u.user_id AS u_user_id,
            u.first_name AS u_first_name,
            u.last_name AS u_last_name 
        FROM
            users u 
        INNER JOIN
            confirmation c 
                ON c.user_id = u.user_id 
        WHERE
            c.access_id = 4 
        ORDER BY
            u.last_name ASC) 
    UNION
    DISTINCT (SELECT
        DISTINCT u.user_id AS u_user_id,
        u.first_name AS u_first_name,
        u.last_name AS u_last_name 
    FROM
        users u 
    INNER JOIN
        employee_access ea 
            ON ea.user_id = u.user_id 
    WHERE
        ea.access_id = 4 
    ORDER BY
        u.last_name ASC)
) AS union1 
ORDER BY
union1.u_last_name ASC

Related Articles



* original question posted on StackOverflow here.