Question

How to optimize this SQL query?

In case you have your own slow SQL query, you can optimize it automatically here.

For the query above, the following recommendations will be helpful as part of the SQL tuning process.
You'll find 3 sections below:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

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.

Optimal indexes for this query:

ALTER TABLE `acat_attribute` ADD INDEX `acat_attribute_idx_autocat_id` (`AUTOCAT_PART_KIT_ID`);
ALTER TABLE `cost_part_kit_attrib_ctlg_pq_icam` ADD INDEX `cost_kit_idx_external_id_attrib_id` (`EXTERNAL_SYS_PART_ID`,`ATTRIB_ID`);
ALTER TABLE `cost_part_kit_attrib_ctlg_pq_icam_prior` ADD INDEX `cost_kit_idx_external_id_attrib_id` (`EXTERNAL_SYS_PART_ID`,`ATTRIB_ID`);

The optimized query:

SELECT
        a.PART_KIT_ID,
        a.`PART_KIT_NAME`,
        a.`EXTERNAL_SYS_PART_ID`,
        a.`ATA_NUMBER`,
        a.`PART_KIT_DESC`,
        a.`ACTIVE_IND`,
        `Flag NameA`,
        `Parameter Name`,
        `Flag NameB`,
        `AutoCAT`,
        `ICAM`,
        `ICAM_PRIOR` 
    FROM
        (SELECT
            b.PART_KIT_ID,
            CASE 
                WHEN b.CTLG_VER_ID <> c.CTLG_VER_ID 
                AND b.CTLG_VER_ID <> a.CTLG_VER_ID THEN 'CTLG VER ID FLAG A' 
                ELSE NULL END AS `Flag NameA`,
CASE 
    WHEN b.CTLG_VER_ID = c.CTLG_VER_ID 
    AND b.CTLG_VER_ID <> a.CTLG_VER_ID THEN 'CTLG VER ID FLAG B' 
    ELSE NULL END AS `Flag NameB`,
a.PART_KIT_NAME `PART_KIT_NAME`,
d.ATA_NUMBER `ATA_NUMBER`,
d.EXTERNAL_SYS_PART_ID `EXTERNAL_SYS_PART_ID`,
d.PART_KIT_DESC `PART_KIT_DESC`,
d.ACTIVE_IND `ACTIVE_IND`,
'CTLG_VER_ID' `Parameter Name`,
a.CTLG_VER_ID `AutoCAT`,
b.CTLG_VER_ID `ICAM`,
c.CTLG_VER_ID `ICAM_PRIOR` 
FROM
cost_part_kit_attrib_ctlg_pq_acat a 
JOIN
cost_part_kit_attrib_ctlg_pq_icam b 
    ON a.EXTERNAL_SYS_PART_ID = b.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = b.ATTRIB_ID 
JOIN
cost_part_kit_attrib_ctlg_pq_icam_prior c 
    ON a.EXTERNAL_SYS_PART_ID = c.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = c.ATTRIB_ID 
LEFT OUTER JOIN
acat_attribute d 
    ON a.ACAT_PART_KIT_ID = d.AUTOCAT_PART_KIT_ID 
UNION
ALL SELECT
b.PART_KIT_ID,
CASE 
    WHEN b.CTLG_NAME <> c.CTLG_NAME 
    AND b.CTLG_NAME <> a.CTLG_NAME THEN 'CTLG NAME FLAG A' 
    ELSE NULL END AS `Flag NameA`,
CASE 
    WHEN b.CTLG_NAME = c.CTLG_NAME 
    AND b.CTLG_NAME <> a.CTLG_NAME THEN 'CTLG NAME FLAG B' 
    ELSE NULL END AS `Flag NameB`,
a.PART_KIT_NAME `PART_KIT_NAME`,
d.ATA_NUMBER `ATA_NUMBER`,
d.EXTERNAL_SYS_PART_ID `EXTERNAL_SYS_PART_ID`,
d.PART_KIT_DESC `PART_KIT_DESC`,
d.ACTIVE_IND `ACTIVE_IND`,
'CTLG_NAME' `Parameter Name`,
a.CTLG_NAME `AutoCAT`,
b.CTLG_NAME `ICAM`,
c.CTLG_NAME `ICAM_PRIOR` 
FROM
cost_part_kit_attrib_ctlg_pq_acat a 
JOIN
cost_part_kit_attrib_ctlg_pq_icam b 
    ON a.EXTERNAL_SYS_PART_ID = b.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = b.ATTRIB_ID 
JOIN
cost_part_kit_attrib_ctlg_pq_icam_prior c 
    ON a.EXTERNAL_SYS_PART_ID = c.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = c.ATTRIB_ID 
LEFT OUTER JOIN
acat_attribute d 
    ON a.ACAT_PART_KIT_ID = d.AUTOCAT_PART_KIT_ID 
UNION
ALL SELECT
b.PART_KIT_ID,
CASE 
    WHEN b.PART_KIT_NAME <> c.PART_KIT_NAME 
    AND b.PART_KIT_NAME <> a.PART_KIT_NAME THEN 'PART KIT NAME FLAG A' 
    ELSE NULL END AS `Flag NameA`,
CASE 
    WHEN b.PART_KIT_NAME = c.PART_KIT_NAME 
    AND b.PART_KIT_NAME <> a.PART_KIT_NAME THEN 'PART KIT NAME FLAG B' 
    ELSE NULL END AS `Flag NameB`,
a.PART_KIT_NAME `PART_KIT_NAME`,
d.ATA_NUMBER `ATA_NUMBER`,
d.EXTERNAL_SYS_PART_ID `EXTERNAL_SYS_PART_ID`,
d.PART_KIT_DESC `PART_KIT_DESC`,
d.ACTIVE_IND `ACTIVE_IND`,
'PART_KIT_NAME' `Parameter Name`,
a.PART_KIT_NAME `AutoCAT`,
b.PART_KIT_NAME `ICAM`,
c.PART_KIT_NAME `ICAM_PRIOR` 
FROM
cost_part_kit_attrib_ctlg_pq_acat a 
JOIN
cost_part_kit_attrib_ctlg_pq_icam b 
    ON a.EXTERNAL_SYS_PART_ID = b.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = b.ATTRIB_ID 
JOIN
cost_part_kit_attrib_ctlg_pq_icam_prior c 
    ON a.EXTERNAL_SYS_PART_ID = c.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = c.ATTRIB_ID 
LEFT OUTER JOIN
acat_attribute d 
    ON a.ACAT_PART_KIT_ID = d.AUTOCAT_PART_KIT_ID 
UNION
ALL SELECT
b.PART_KIT_ID,
CASE 
    WHEN b.ATTRIB_ID <> c.ATTRIB_ID 
    AND b.ATTRIB_ID <> a.ATTRIB_ID THEN 'ATTRIB_ID FLAG A' 
    ELSE NULL END AS `Flag NameA`,
CASE 
    WHEN b.ATTRIB_ID = b.ATTRIB_ID 
    AND b.ATTRIB_ID <> a.ATTRIB_ID THEN 'ATTRIB_ID ID FLAG B' 
    ELSE NULL END AS `Flag NameB`,
a.PART_KIT_NAME `PART_KIT_NAME`,
d.ATA_NUMBER `ATA_NUMBER`,
d.EXTERNAL_SYS_PART_ID `EXTERNAL_SYS_PART_ID`,
d.PART_KIT_DESC `PART_KIT_DESC`,
d.ACTIVE_IND `ACTIVE_IND`,
'ATTRIB_ID' `Parameter Name`,
a.ATTRIB_ID `AutoCAT`,
b.ATTRIB_ID `ICAM`,
c.ATTRIB_ID `ICAM_PRIOR` 
FROM
cost_part_kit_attrib_ctlg_pq_acat a 
JOIN
cost_part_kit_attrib_ctlg_pq_icam b 
    ON a.EXTERNAL_SYS_PART_ID = b.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = b.ATTRIB_ID 
JOIN
cost_part_kit_attrib_ctlg_pq_icam_prior c 
    ON a.EXTERNAL_SYS_PART_ID = c.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = c.ATTRIB_ID 
LEFT OUTER JOIN
acat_attribute d 
    ON a.ACAT_PART_KIT_ID = d.AUTOCAT_PART_KIT_ID 
UNION
ALL SELECT
b.PART_KIT_ID,
CASE 
    WHEN b.ATTRIB_NAME <> c.ATTRIB_NAME 
    AND b.ATTRIB_NAME <> a.ATTRIB_NAME THEN 'ATTRIB_NAME FLAGA' 
    ELSE NULL END AS `Flag NameA`,
CASE 
    WHEN b.ATTRIB_NAME = c.ATTRIB_NAME 
    AND b.ATTRIB_NAME <> a.ATTRIB_NAME THEN 'ATTRIB_NAME FLAGB' 
    ELSE NULL END AS `Flag NameB`,
a.PART_KIT_NAME `PART_KIT_NAME`,
d.ATA_NUMBER `ATA_NUMBER`,
d.EXTERNAL_SYS_PART_ID `EXTERNAL_SYS_PART_ID`,
d.PART_KIT_DESC `PART_KIT_DESC`,
d.ACTIVE_IND `ACTIVE_IND`,
'ATTRIB_NAME' `Parameter Name`,
a.ATTRIB_NAME `AutoCAT`,
b.ATTRIB_NAME `ICAM`,
c.ATTRIB_NAME `ICAM_PRIOR` 
FROM
cost_part_kit_attrib_ctlg_pq_acat a 
JOIN
cost_part_kit_attrib_ctlg_pq_icam b 
    ON a.EXTERNAL_SYS_PART_ID = b.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = b.ATTRIB_ID 
JOIN
cost_part_kit_attrib_ctlg_pq_icam_prior c 
    ON a.EXTERNAL_SYS_PART_ID = c.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = c.ATTRIB_ID 
LEFT OUTER JOIN
acat_attribute d 
    ON a.ACAT_PART_KIT_ID = d.AUTOCAT_PART_KIT_ID 
UNION
ALL SELECT
b.PART_KIT_ID,
CASE 
    WHEN b.ATTRIB_DESC <> c.ATTRIB_DESC 
    AND b.ATTRIB_DESC <> a.ATTRIB_DESC THEN 'ATTRIB_DESC FLAGA' 
    ELSE NULL END AS `Flag NameA`,
CASE 
    WHEN b.ATTRIB_DESC = c.ATTRIB_DESC 
    AND b.ATTRIB_DESC <> a.ATTRIB_DESC THEN 'ATTRIB_DESC FLAGB' 
    ELSE NULL END AS `Flag NameB`,
a.PART_KIT_NAME `PART_KIT_NAME`,
d.ATA_NUMBER `ATA_NUMBER`,
d.EXTERNAL_SYS_PART_ID `EXTERNAL_SYS_PART_ID`,
d.PART_KIT_DESC `PART_KIT_DESC`,
d.ACTIVE_IND `ACTIVE_IND`,
'ATTRIB_DESC' `Parameter Name`,
a.ATTRIB_DESC `AutoCAT`,
b.ATTRIB_DESC `ICAM`,
c.ATTRIB_DESC `ICAM_PRIOR` 
FROM
cost_part_kit_attrib_ctlg_pq_acat a 
JOIN
cost_part_kit_attrib_ctlg_pq_icam b 
    ON a.EXTERNAL_SYS_PART_ID = b.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = b.ATTRIB_ID 
JOIN
cost_part_kit_attrib_ctlg_pq_icam_prior c 
    ON a.EXTERNAL_SYS_PART_ID = c.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = c.ATTRIB_ID 
LEFT OUTER JOIN
acat_attribute d 
    ON a.ACAT_PART_KIT_ID = d.AUTOCAT_PART_KIT_ID 
UNION
ALL SELECT
b.PART_KIT_ID,
CASE 
    WHEN b.ATTRIB_VALUE <> c.ATTRIB_VALUE 
    AND b.ATTRIB_VALUE <> a.ATTRIB_VALUE THEN 'ATTRIB_VALUE FLAGA' 
    ELSE NULL END AS `Flag NameA`,
CASE 
    WHEN b.ATTRIB_VALUE = c.ATTRIB_VALUE 
    AND b.ATTRIB_VALUE <> a.ATTRIB_VALUE THEN 'ATTRIB_VALUE FLAGB' 
    ELSE NULL END AS `Flag NameB`,
a.PART_KIT_NAME `PART_KIT_NAME`,
d.ATA_NUMBER `ATA_NUMBER`,
d.EXTERNAL_SYS_PART_ID `EXTERNAL_SYS_PART_ID`,
d.PART_KIT_DESC `PART_KIT_DESC`,
d.ACTIVE_IND `ACTIVE_IND`,
'ATTRIB_VALUE' `Parameter Name`,
a.ATTRIB_VALUE `AutoCAT`,
b.ATTRIB_VALUE `ICAM`,
c.ATTRIB_VALUE `ICAM_PRIOR` 
FROM
cost_part_kit_attrib_ctlg_pq_acat a 
JOIN
cost_part_kit_attrib_ctlg_pq_icam b 
    ON a.EXTERNAL_SYS_PART_ID = b.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = b.ATTRIB_ID 
JOIN
cost_part_kit_attrib_ctlg_pq_icam_prior c 
    ON a.EXTERNAL_SYS_PART_ID = c.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = c.ATTRIB_ID 
LEFT OUTER JOIN
acat_attribute d 
    ON a.ACAT_PART_KIT_ID = d.AUTOCAT_PART_KIT_ID 
UNION
ALL SELECT
b.PART_KIT_ID,
CASE 
    WHEN b.EXTERNAL_SYS_PART_ID <> c.EXTERNAL_SYS_PART_ID 
    AND b.EXTERNAL_SYS_PART_ID <> a.EXTERNAL_SYS_PART_ID THEN 'EXTERNAL_SYS_PART_ID FLAGA' 
    ELSE NULL END AS `Flag NameA`,
CASE 
    WHEN b.EXTERNAL_SYS_PART_ID = c.EXTERNAL_SYS_PART_ID 
    AND b.EXTERNAL_SYS_PART_ID <> a.EXTERNAL_SYS_PART_ID THEN 'EXTERNAL_SYS_PART_ID FLAGB' 
    ELSE NULL END AS `Flag NameB`,
a.PART_KIT_NAME `PART_KIT_NAME`,
d.ATA_NUMBER `ATA_NUMBER`,
d.EXTERNAL_SYS_PART_ID `EXTERNAL_SYS_PART_ID`,
d.PART_KIT_DESC `PART_KIT_DESC`,
d.ACTIVE_IND `ACTIVE_IND`,
'EXTERNAL_SYS_PART_ID' `Parameter Name`,
a.EXTERNAL_SYS_PART_ID `AutoCAT`,
b.EXTERNAL_SYS_PART_ID `ICAM`,
c.EXTERNAL_SYS_PART_ID `ICAM_PRIOR` 
FROM
cost_part_kit_attrib_ctlg_pq_acat a 
JOIN
cost_part_kit_attrib_ctlg_pq_icam b 
    ON a.EXTERNAL_SYS_PART_ID = b.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = b.ATTRIB_ID 
JOIN
cost_part_kit_attrib_ctlg_pq_icam_prior c 
    ON a.EXTERNAL_SYS_PART_ID = c.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = c.ATTRIB_ID 
LEFT OUTER JOIN
acat_attribute d 
    ON a.ACAT_PART_KIT_ID = d.AUTOCAT_PART_KIT_ID 
UNION
ALL SELECT
b.PART_KIT_ID,
CASE 
    WHEN b.PART_KIT_ID <> c.PART_KIT_ID 
    AND b.PART_KIT_ID <> a.PART_KIT_ID THEN 'PART_KIT_ID FLAGA' 
    ELSE NULL END AS `Flag NameA`,
CASE 
    WHEN b.PART_KIT_ID = c.PART_KIT_ID 
    AND b.PART_KIT_ID <> a.PART_KIT_ID THEN 'PART_KIT_ID FLAGB' 
    ELSE NULL END AS `Flag NameB`,
a.PART_KIT_NAME `PART_KIT_NAME`,
d.ATA_NUMBER `ATA_NUMBER`,
d.EXTERNAL_SYS_PART_ID `EXTERNAL_SYS_PART_ID`,
d.PART_KIT_DESC `PART_KIT_DESC`,
d.ACTIVE_IND `ACTIVE_IND`,
'PART_KIT_ID' `Parameter Name`,
a.PART_KIT_ID `AutoCAT`,
b.PART_KIT_ID `ICAM`,
c.PART_KIT_ID `ICAM_PRIOR` 
FROM
cost_part_kit_attrib_ctlg_pq_acat a 
JOIN
cost_part_kit_attrib_ctlg_pq_icam b 
    ON a.EXTERNAL_SYS_PART_ID = b.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = b.ATTRIB_ID 
JOIN
cost_part_kit_attrib_ctlg_pq_icam_prior c 
    ON a.EXTERNAL_SYS_PART_ID = c.EXTERNAL_SYS_PART_ID 
    AND a.ATTRIB_ID = c.ATTRIB_ID 
LEFT OUTER JOIN
acat_attribute d 
    ON a.ACAT_PART_KIT_ID = d.AUTOCAT_PART_KIT_ID
) a 
ORDER BY
a.PART_KIT_ID

How to optimize this SQL query?

The optimization process and recommendations:

Optimal indexes for this query:

The optimized query:

Related Articles