mysql nested level query getting very slow

I have a MySQL query containing two columns that cause a delay of 25 seconds when the query is wrapped, while the inner query runs fine within 2 seconds. Both columns have already been indexed, and I have tried reducing joins to optimize the query, also replaced subqueries with joins, which reduced times by 8,9 seconds. but it did not provide significant performance improvements. Here is the original nested SQL query:

SELECT
    first_base.case_id_c
FROM (
    SELECT DISTINCT
        (cc.case_id_c),
        (
            SELECT CONCAT(u.first_name, ' ', u.last_name)
            FROM users u
            WHERE u.id = cc.user_id1_c
        ) AS Case_Advocate,
        (
            SELECT CONCAT(u.first_name, ' ', u.last_name)
            FROM users u
            WHERE u.id = cc.user_id2_c
        ) AS Practitioner,
        (
            SELECT CONCAT(u.first_name, ' ', u.last_name)
            FROM users u
            WHERE u.id = cc.user_id3_c
        ) AS Tax_Preparer,
        c.id,
        rtpc.service_level_c AS tp_service_level, -- causing delay 
        cc.tax_preparation_level_c AS client_tax_prep_service_level,
        rtpc.id_c AS 'TaxPrepID',-- causing delay 
        (
            CASE WHEN rtp.deleted IS NULL THEN 0 ELSE rtp.deleted
        END
        ) AS TP_DELETED
    FROM contacts c
    LEFT JOIN contacts_cstm cc ON cc.id_c = c.id
    LEFT JOIN contacts_reso_resolutions_1_c crr1c ON crr1c.contacts_reso_resolutions_1contacts_ida = cc.id_c
    LEFT JOIN reso_resolutions_cstm AS rrc ON rrc.id_c = crr1c.contacts_reso_resolutions_1reso_resolutions_idb
    LEFT JOIN reso_resolutions AS rr ON rr.id = rrc.id_c
    LEFT JOIN contacts_reso_ancillary_services_1_c AS cras1c ON cras1c.contacts_reso_ancillary_services_1contacts_ida = c.id
    LEFT JOIN reso_ancillary_services_cstm AS rasc ON rasc.id_c = cras1c.contacts_reso_ancillary_services_1reso_ancillary_services_idb
    LEFT JOIN reso_ancillary_services AS ras ON ras.id = rasc.id_c
    LEFT JOIN contacts_reso_tax_preparation_1_c AS crtp1c ON crtp1c.contacts_reso_tax_preparation_1contacts_ida = c.id
    LEFT JOIN reso_tax_preparation_cstm AS rtpc ON rtpc.id_c = crtp1c.contacts_reso_tax_preparation_1reso_tax_preparation_idb
    LEFT JOIN reso_tax_preparation AS rtp ON rtp.id = rtpc.id_c
    WHERE c.deleted <> 1 AND cc.ctax_status_c = 'Active Service' AND preferred_language NOT LIKE '%en_us%'
) AS first_base

The problematic columns in the query are:

rtpc.id_c AS 'TaxPrepID',
rtpc.service_level_c AS tp_service_level,

I also ran an EXPLAIN query on the SQL but it did not seem to help much. The ctax_status_c column only appeared in the WHERE clause with the value 'Active Service'. Is there anything else I can do to optimize the query performance?

enter image description here



Comments

Popular posts from this blog

Today Walkin 14th-Sept

Hibernate Search - Elasticsearch with JSON manipulation

Spring Elasticsearch Operations