CODEWITHSUNDEEP
mysqlsqlquery-performance
Ashish Shah
Ashish Shah

Reputation: 33

Mysql query taking 20 minutes only for 11000 records. how to optimize below mysql query select query in where clause with not exists

SELECT DISTINCT ACA.Application_No, AC.FirstName,AC.Id,AC.LastName,AC.MobileNo,CL.leadId
        FROM ABSLI_PAYMENT_TRANSACTION APT
             INNER JOIN ABSLI_CUSTOMER_APPLICATION ACA ON ACA.Policy_No=APT.policyId
             INNER JOIN ABSLI_CUSTOMER AC ON AC.Id=ACA.CustomerId
             LEFT JOIN ABSLI_CUSTOMER_LEAD CL ON CL.policyId = ACA.Policy_No
             INNER JOIN ABSLI_Policy_Status_Tracking pst ON pst.policyId = APT.policyId
        WHERE APT.paymentStatus='Y'
             AND NOT EXISTS (SELECT 1 FROM ABSLI_SERVICE_STATUS WHERE PolicyNo=APT.policyId AND NAME = 'APEX_Validate')
             AND ACA.Application_No NOT IN (SELECT RT.ApplicationNumber FROM ABSLI_REFUND_TRANSACTION RT WHERE RT.Status != 'Retain')
        ORDER BY pst.updatedDate DESC;

Upvotes: 0

Views: 141

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

Considering you have an index on ABSLI_REFUND_TRANSACTION.ApplicationNumber column, you can try to use NOT EXISTS also for the second subquery instead of NOT IN. This way, you benefit this index for that subquery, and usually NOT IN might be problematic for large set of data.

SELECT DISTINCT ACA.Application_No, AC.FirstName, AC.Id, AC.LastName, AC.MobileNo, CL.leadId
  FROM ABSLI_PAYMENT_TRANSACTION APT
 INNER JOIN ABSLI_CUSTOMER_APPLICATION ACA
    ON ACA.Policy_No = APT.policyId
 INNER JOIN ABSLI_CUSTOMER AC
    ON AC.Id = ACA.CustomerId
  LEFT JOIN ABSLI_CUSTOMER_LEAD CL
    ON CL.policyId = ACA.Policy_No
 INNER JOIN ABSLI_Policy_Status_Tracking pst
    ON pst.policyId = APT.policyId
 WHERE APT.paymentStatus = 'Y'
   AND NOT EXISTS (SELECT 1
                     FROM ABSLI_SERVICE_STATUS
                    WHERE PolicyNo = APT.policyId
                      AND NAME = 'APEX_Validate')
   AND NOT EXISTS (SELECT 1
                     FROM ABSLI_REFUND_TRANSACTION RT
                    WHERE RT.Status != 'Retain'
                      AND RT.ApplicationNumber = ACA.Application_No)
 ORDER BY pst.updatedDate DESC;

But without knowing the execution plan, hard to tell more about performance.

Upvotes: 0

Uueerdo
Uueerdo

Reputation: 15941

Correlated subqueries can be costly on larger datasets, you might want to try converting

...
AND NOT EXISTS (SELECT 1 FROM ABSLI_SERVICE_STATUS WHERE PolicyNo=APT.policyId AND NAME = 'APEX_Validate')
...

to something like

...
LEFT JOIN ABSLI_SERVICE_STATUS AS ss ON APT.policyId = ss.PolicyNo AND ss.NAME = 'APEX_Validate'
...
AND ss.NAME IS NULL

Normally, I'd suggest an "id" field from ss for the IS NULL check, but NAME obviously exists (from the query you have) and cannot be 'APEX_Validate' and NULL at the same time. Also, if there is a compound index on PolicyNo, Name that index can probably be used without accessing the table itself.)

Upvotes: 0

Related Questions