EverSQL Database Performance Knowledge Base

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