EverSQL Database Performance Knowledge Base

The optimization process and recommendations:

1. 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.
2. Remove Redundant Left Joins (modified query below): Redundant LEFT JOINs (e.g. `table_stfgr`) 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.
3. Replace In Subquery With Correlated Exists (modified query below): In many cases, an EXISTS subquery with a correlated condition will perform better than a non correlated IN subquery.

Optimal indexes for this query:

ALTER TABLE `table_arcons` ADD INDEX `table_arcons_idx_acid` (`acid`);
ALTER TABLE `table_arstaff` ADD INDEX `table_arstaff_idx_acid` (`acid`);
ALTER TABLE `table_stf` ADD INDEX `table_stf_idx_sid` (`sid`);

The optimized query:

SELECT
        SA0_.sid AS stid 
    FROM
        table_starea SA0_ 
    LEFT OUTER JOIN
        table_arcons AC1_ 
            ON SA0_.acid = AC1_.acid 
    WHERE
        EXISTS (
            SELECT
                1 
            FROM
                table_arstaff AC0_ 
            LEFT OUTER JOIN
                table_stf SB1_ 
                    ON AC0_.sid = SB1_.sid 
            WHERE
                (
                    EXISTS (
                        SELECT
                            1 
                        FROM
                            table_starea SA0_ 
                        LEFT OUTER JOIN
                            table_arcons AC1_ 
                                ON SA0_.acid = AC1_.acid 
                        WHERE
                            (
                                EXISTS (
                                    SELECT
                                        1 
                                    FROM
                                        table_arstaff AC0_ 
                                    LEFT OUTER JOIN
                                        table_stf SB1_ 
                                            ON AC0_.sid = SB1_.sid 
                                    WHERE
                                        (
                                            EXISTS (
                                                SELECT
                                                    1 
                                                FROM
                                                    table_starea SA0_ 
                                                LEFT OUTER JOIN
                                                    table_arcons AC1_ 
                                                        ON SA0_.acid = AC1_.acid 
                                                WHERE
                                                    (
                                                        EXISTS (
                                                            SELECT
                                                                1 
                                                            FROM
                                                                table_arstaff AC0_ 
                                                            LEFT OUTER JOIN
                                                                table_stf SB1_ 
                                                                    ON AC0_.sid = SB1_.sid 
                                                            WHERE
                                                                (
                                                                    EXISTS (
                                                                        SELECT
                                                                            1 
                                                                        FROM
                                                                            table_starea SA0_ 
                                                                        LEFT OUTER JOIN
                                                                            table_arcons AC1_ 
                                                                                ON SA0_.acid = AC1_.acid 
                                                                        WHERE
                                                                            (
                                                                                EXISTS (
                                                                                    SELECT
                                                                                        1 
                                                                                    FROM
                                                                                        table_arstaff AC0_ 
                                                                                    LEFT OUTER JOIN
                                                                                        table_stf SB1_ 
                                                                                            ON AC0_.sid = SB1_.sid 
                                                                                    WHERE
                                                                                        (
                                                                                            EXISTS (
                                                                                                SELECT
                                                                                                    1 
                                                                                                FROM
                                                                                                    table_starea SA0_ 
                                                                                                LEFT OUTER JOIN
                                                                                                    table_arcons AC1_ 
                                                                                                        ON SA0_.acid = AC1_.acid 
                                                                                                WHERE
                                                                                                    (
                                                                                                        EXISTS (
                                                                                                            SELECT
                                                                                                                1 
                                                                                                            FROM
                                                                                                                table_arstaff AC0_ 
                                                                                                            LEFT OUTER JOIN
                                                                                                                table_stf SB1_ 
                                                                                                                    ON AC0_.sid = SB1_.sid 
                                                                                                            WHERE
                                                                                                                (
                                                                                                                    EXISTS (
                                                                                                                        SELECT
                                                                                                                            1 
                                                                                                                        FROM
                                                                                                                            table_starea SA0_ 
                                                                                                                        LEFT OUTER JOIN
                                                                                                                            table_arcons AC1_ 
                                                                                                                                ON SA0_.acid = AC1_.acid 
                                                                                                                        WHERE
                                                                                                                            (
                                                                                                                                EXISTS (
                                                                                                                                    SELECT
                                                                                                                                        1 
                                                                                                                                    FROM
                                                                                                                                        table_arstaff AC0_ 
                                                                                                                                    LEFT OUTER JOIN
                                                                                                                                        table_stf SB1_ 
                                                                                                                                            ON AC0_.sid = SB1_.sid 
                                                                                                                                    WHERE
                                                                                                                                        (
                                                                                                                                            AC0_.acid = 19
                                                                                                                                        ) 
                                                                                                                                        AND (
                                                                                                                                            SA0_.sid = SB1_.sid
                                                                                                                                        )
                                                                                                                                )
                                                                                                                            ) 
                                                                                                                            AND (
                                                                                                                                AC0_.acid = AC1_.acid
                                                                                                                            )
                                                                                                                        )
                                                                                                                ) 
                                                                                                                AND (
                                                                                                                    SA0_.sid = SB1_.sid
                                                                                                                )
                                                                                                            )
                                                                                                        ) 
                                                                                                        AND (
                                                                                                            AC0_.acid = AC1_.acid
                                                                                                        )
                                                                                                )
                                                                                            ) 
                                                                                            AND (
                                                                                                SA0_.sid = SB1_.sid
                                                                                            )
                                                                                        )
                                                                                ) 
                                                                                AND (
                                                                                    AC0_.acid = AC1_.acid
                                                                                )
                                                                            )
                                                                        ) 
                                                                        AND (
                                                                            SA0_.sid = SB1_.sid
                                                                        )
                                                                )
                                                            ) 
                                                            AND (
                                                                AC0_.acid = AC1_.acid
                                                            )
                                                        )
                                                ) 
                                                AND (
                                                    SA0_.sid = SB1_.sid
                                                )
                                            )
                                        ) 
                                        AND (
                                            AC0_.acid = AC1_.acid
                                        )
                                )
                            ) 
                            AND (
                                SA0_.sid = SB1_.sid
                            )
                        )