Am trying to join two table base on status and also some json_contains query. This query is working fine for me, I just want to optimize as it taking too much time.

I have already put necessary indexing.

SELECT
    ca.id AS customer_application_id,
    ca.partner_id AS application_partner_id,
    ca.seller_id AS application_seller_id,
    ca.status AS application_status,
    ca.overall_status AS application_overall_status,
    lc.id AS customer_id,
    lc.seller_id,
    lc.pre_registration_id,
    lc.status AS customer_status,
    lc.overall_status AS customer_overall_status,
    lc.deleted_at AS customer_deleted_at
FROM
    customer_applications ca
LEFT JOIN
    (SELECT
        c.id,
        c.pre_registration_id,
        c.status,
        c.seller_id,
        c.overall_status,
        c.deleted_at
    FROM
        customers c
    WHERE
        c.deleted_at IS NULL AND
         c.status NOT IN (30, 40) AND 
        c.id = (
            SELECT MAX(csub.id)
            FROM customers csub
            WHERE csub.pre_registration_id = c.pre_registration_id
            AND csub.deleted_at IS NULL
            AND csub.status NOT IN (30, 40)
        )) lc ON ca.id = lc.pre_registration_id
WHERE
1 = 1
    AND (ca.seller_id IN (2, 4) 
        OR lc.seller_id IN (2, 4)
    )
    AND ca.partner_id = 1
    AND ca.mode = 10
    AND (lc.deleted_at IS NULL OR ca.customer_id IS NULL) 
    AND (lc.status NOT IN (30, 40) OR ca.customer_id IS NULL)
    AND (ca.is_reopen = 1 
       OR (json_contains(CAST(ca.`data` AS JSON), 'true', '$."set_the_basics_finished"')
           AND json_contains(CAST(ca.`data` AS JSON), 'true', '$."mcc_and_turnover_finished"')
           AND json_contains(CAST(ca.`data` AS JSON), 'true', '$."card_rates_and_fees_finished"')
           AND json_contains(CAST(ca.`data` AS JSON), 'true', '$."business_type_finished"')))
ORDER BY
    ca.created_at DESC,
    lc.pre_registration_id DESC;

I have implemeting indexing. Expecting more effecent query.

Am trying to join two table base on status and also some json_contains query. This query is working fine for me, I just want to optimize as it taking too much time.

I have already put necessary indexing.

SELECT
    ca.id AS customer_application_id,
    ca.partner_id AS application_partner_id,
    ca.seller_id AS application_seller_id,
    ca.status AS application_status,
    ca.overall_status AS application_overall_status,
    lc.id AS customer_id,
    lc.seller_id,
    lc.pre_registration_id,
    lc.status AS customer_status,
    lc.overall_status AS customer_overall_status,
    lc.deleted_at AS customer_deleted_at
FROM
    customer_applications ca
LEFT JOIN
    (SELECT
        c.id,
        c.pre_registration_id,
        c.status,
        c.seller_id,
        c.overall_status,
        c.deleted_at
    FROM
        customers c
    WHERE
        c.deleted_at IS NULL AND
         c.status NOT IN (30, 40) AND 
        c.id = (
            SELECT MAX(csub.id)
            FROM customers csub
            WHERE csub.pre_registration_id = c.pre_registration_id
            AND csub.deleted_at IS NULL
            AND csub.status NOT IN (30, 40)
        )) lc ON ca.id = lc.pre_registration_id
WHERE
1 = 1
    AND (ca.seller_id IN (2, 4) 
        OR lc.seller_id IN (2, 4)
    )
    AND ca.partner_id = 1
    AND ca.mode = 10
    AND (lc.deleted_at IS NULL OR ca.customer_id IS NULL) 
    AND (lc.status NOT IN (30, 40) OR ca.customer_id IS NULL)
    AND (ca.is_reopen = 1 
       OR (json_contains(CAST(ca.`data` AS JSON), 'true', '$."set_the_basics_finished"')
           AND json_contains(CAST(ca.`data` AS JSON), 'true', '$."mcc_and_turnover_finished"')
           AND json_contains(CAST(ca.`data` AS JSON), 'true', '$."card_rates_and_fees_finished"')
           AND json_contains(CAST(ca.`data` AS JSON), 'true', '$."business_type_finished"')))
ORDER BY
    ca.created_at DESC,
    lc.pre_registration_id DESC;

I have implemeting indexing. Expecting more effecent query.

• mysql

• Please share the output of explain and all the indexes in the underlying tables. – Shadow Commented Mar 19 at 18:48

1 Answer 1

The query plan provides the most important information for you, which you can look into using the explain <yourquery> command. But look into your query:

• you have a left-hand-side table, aliased as ca
• you left join it with a subquery(!)
• and inside the subquery you have another subquery

Instead, for the MAX you could have a grouped with statement:

WITH cte AS
(
            SELECT MAX(csub.id) as id, csub.pre_registration_id
            FROM customers csub
            WHERE
            AND csub.deleted_at IS NULL
            AND csub.status NOT IN (30, 40)
            GROUP BY csub.pre_registration_id
)
<your query here>

and then you can join or left join with cte, like:

<your cte here>
SELECT ...
FROM customer_applications ca
LEFT JOIN customers c
ON ...
LEFT JOIN cte
ON ...

So, instead of subqueries you would have a joined query and you would compute the grouped cte instead of computing it for each item in a subselect.