[Solved] Slowness in query having many nested in clauses

EverSQL Database Performance Knowledge Base

Slowness in query having many nested in clauses

I am facing slowness in a query having many nested in clauses.

The query is given below:

select SA0_.sid as stid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid where SA0_.sid in (select SB1_.sid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid where AC0_.acid in (select AC1_.acid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid where SA0_.sid in (select SB1_.sid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid where AC0_.acid in (select AC1_.acid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid where SA0_.sid in (select SB1_.sid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid where AC0_.acid in (select AC1_.acid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid where SA0_.sid in (select SB1_.sid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid where AC0_.acid in (select AC1_.acid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid where SA0_.sid in (select SB1_.sid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid where AC0_.acid in (select AC1_.acid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid where SA0_.sid in (select SB1_.sid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid where AC0_.acid=19)))))))))));

I have checked explain for the same and found like below:

+----+--------------+-------------------+------------+------------+----------------------------------------+---------------------+---------+----------------------------------------------------+------+----------+--------------------------+
| id | select_type  | table     | partitions | type       | possible_keys                          | key                 | key_len | ref                        | rows | filtered | Extra                    |
+----+--------------+-------------------+------------+------------+----------------------------------------+---------------------+---------+----------------------------------------------------+------+----------+--------------------------+
|  1 | SIMPLE       | <subquery2>   | NULL       | ALL        | NULL                                   | NULL                | NULL    | NULL                       | NULL |   100.00 | Using where              |
|  1 | SIMPLE       | SA0_      | NULL       | ref        | table_starea_ix01                      | table_starea_ix01   | 5       | <subquery2>.sid                    |    1 |   100.00 | Using index              |
|  1 | SIMPLE       | AC1_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.SA0_.acid                |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | AC0_      | NULL       | ref        | table_arstaff_ix01,tab_attach_fk2      | table_arstaff_ix01  | 4       | const                      |    4 |   100.00 | Using where; Using index |
|  2 | MATERIALIZED | SB1_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.AC0_.sid             |    1 |   100.00 | NULL                     |
|  2 | MATERIALIZED | SG2_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.SB1_.sgid                |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | SA0_      | NULL       | ref        | FKFA890976F203761F,table_starea_ix01   | table_starea_ix01   | 5       | ain2013Realdb.AC0_.sid             |    1 |   100.00 | Using where; Using index |
|  2 | MATERIALIZED | AC1_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.SA0_.acid                |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | AC0_      | NULL       | ref        | table_arstaff_ix01,tab_attach_fk2      | table_arstaff_ix01  | 4       | ain2013Realdb.SA0_.acid                |    2 |   100.00 | Using where; Using index |
|  2 | MATERIALIZED | SB1_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.AC0_.sid             |    1 |   100.00 | NULL                     |
|  2 | MATERIALIZED | SG2_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.SB1_.sgid                |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | SA0_      | NULL       | ref        | FKFA890976F203761F,table_starea_ix01   | table_starea_ix01   | 5       | ain2013Realdb.AC0_.sid             |    1 |   100.00 | Using where; Using index |
|  2 | MATERIALIZED | AC1_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.SA0_.acid                |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | AC0_      | NULL       | ref        | table_arstaff_ix01,tab_attach_fk2      | table_arstaff_ix01  | 4       | ain2013Realdb.SA0_.acid                |    2 |   100.00 | Using index              |
|  2 | MATERIALIZED | SB1_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.AC0_.sid             |    1 |   100.00 | NULL                     |
|  2 | MATERIALIZED | SG2_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.SB1_.sgid                |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | SA0_      | NULL       | ref        | FKFA890976F203761F,table_starea_ix01   | table_starea_ix01   | 5       | ain2013Realdb.AC0_.sid             |    1 |   100.00 | Using where; Using index |
|  2 | MATERIALIZED | AC1_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.SA0_.acid                |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | AC0_      | NULL       | ref        | table_arstaff_ix01,tab_attach_fk2      | table_arstaff_ix01  | 4       | ain2013Realdb.SA0_.acid                |    2 |   100.00 | Using where; Using index |
|  2 | MATERIALIZED | SB1_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.AC0_.sid             |    1 |   100.00 | NULL                     |
|  2 | MATERIALIZED | SG2_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.SB1_.sgid                |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | SA0_      | NULL       | ref        | FKFA890976F203761F,table_starea_ix01   | table_starea_ix01   | 5       | ain2013Realdb.AC0_.sid             |    1 |   100.00 | Using where; Using index |
|  2 | MATERIALIZED | AC1_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.SA0_.acid                |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | AC0_      | NULL       | ref        | table_arstaff_ix01,tab_attach_fk2      | table_arstaff_ix01  | 4       | ain2013Realdb.SA0_.acid                |    2 |   100.00 | Using index              |
|  2 | MATERIALIZED | SB1_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.AC0_.sid             |    1 |   100.00 | NULL                     |
|  2 | MATERIALIZED | SG2_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.SB1_.sgid                |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | SA0_      | NULL       | ref        | FKFA890976F203761F,table_starea_ix01   | table_starea_ix01   | 5       | ain2013Realdb.AC0_.sid             |    1 |   100.00 | Using where; Using index |
|  2 | MATERIALIZED | AC1_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.SA0_.acid                |    1 |   100.00 | Using index              |
|  2 | MATERIALIZED | AC0_      | NULL       | ref        | table_arstaff_ix01,tab_attach_fk2      | table_arstaff_ix01  | 4       | ain2013Realdb.SA0_.acid                |    2 |   100.00 | Using index              |
|  2 | MATERIALIZED | SB1_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.AC0_.sid             |    1 |   100.00 | NULL                     |
|  2 | MATERIALIZED | SG2_      | NULL       | eq_ref     | PRIMARY                                | PRIMARY             | 4       | ain2013Realdb.SB1_.sgid                |    1 |   100.00 | Using index              |
+----+--------------+-------------------+------------+------------+----------------------------------------+---------------------+---------+----------------------------------------------------+------+----------+--------------------------+

Can anybody suggest a solution to make this query execute fast?

I rewrote the query using INNER JOIN as below:

select SA0_.sid as stid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid where SA0_.sid in (select distinct sid from ((select SB1_.sid, AC0_.acid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid) TAB19 INNER JOIN (select acid from ((select AC1_.acid,SA0_.sid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid) TAB17 INNER JOIN (select sid from ((select SB1_.sid, AC0_.acid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid) TAB15 INNER JOIN (select acid from ((select AC1_.acid, SA0_.sid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid) TAB13 INNER JOIN (select sid from ((select SB1_.sid, AC0_.acid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid) TAB11 INNER JOIN (select acid from ((select AC1_.acid, SA0_.sid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid) TAB9 INNER JOIN (select sid from ((select SB1_.sid, AC0_.acid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid) TAB7 INNER JOIN (select acid from ((select AC1_.acid, SA0_.sid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid) TAB5 INNER JOIN (select sid from (select SB1_.sid, AC0_.acid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid) TAB3 INNER JOIN (select acid from (select AC1_.acid, SA0_.sid from table_starea SA0_ left outer join table_arcons AC1_ on SA0_.acid=AC1_.acid) TAB2 INNER JOIN (select SB1_.sid from table_arstaff AC0_ left outer join table_stf SB1_ on AC0_.sid=SB1_.sid left outer join table_stfgr SG2_ on SB1_.sgid=SG2_.sgid where AC0_.acid=19) TAB1 ON TAB2.sid = TAB1.sid) TAB4 on TAB3.acid = TAB4.acid) TAB6 on TAB5.sid = TAB6.sid)) TAB8 ON TAB7.acid = TAB8.acid)) TAB10 on TAB9.sid = TAB10.sid)) TAB12 on TAB11.acid = TAB12.acid)) TAB14 on TAB13.sid = TAB14.sid)) TAB16 on TAB15.acid = TAB16.acid)) TAB18 on TAB17.sid = TAB18.sid)) TAB20 on TAB19.acid = TAB20.acid));

Will this bring optimization?

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. 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
                            )
                        )

Related Articles



* original question posted on StackOverflow here.