Announcement

Announcement Module
Collapse
No announcement yet.

Please help to optimize slow running query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Please help to optimize slow running query

    Hi all,

    I am using mysql 4 on solaris 8.
    Following is a query which takes 69s to execute.


    select cc.creditCardNumber as creditCardNumber,iph.name as individualPolicyHolderName, pc.uuid as policyCertificateId,pc.certificateNumber as certificateNumber FROM CreditCard cc,IndividualPolicyHolder iph,PolicyCertificate pc, IndividualPolicy ip where cc.uuid= pc.individualPolicyHolderId and pc.individualPolicyHolderId = iph.uuid andpc.pioId =800001169443885743 and (select count(ip.uuid) from IndividualPolicy ip where ip.policyCertificateUUId=pc.uuid and ip.endDate is null and ip.policyHolderUUId=pc.individualPolicyHolderId and ip.policyHolderUUId=iph.uuid ) > 0 group by pc.uuid order by pc.startDate desc;

    The explain of the perticular query is

    *************************** 1. row *************************** id: 1 select_type: PRIMARY table: pc type: refpossible_keys: PC_IndividualPolicyHolder,PC_PIO key: PC_PIO key_len: 8 ref: const rows: 392 Extra: Using where; Using temporary; Using filesort*************************** 2. row *************************** id: 1 select_type: PRIMARY table: iph type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: jgrtest_new.pc.individualPolicyHolderId rows: 1 Extra: Using where*************************** 3. row *************************** id: 1 select_type: PRIMARY table: cc type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 8 ref: jgrtest_new.pc.individualPolicyHolderId rows: 1 Extra:*************************** 4. row *************************** id: 1 select_type: PRIMARY table: ip type: indexpossible_keys: NULL key: PRIMARY key_len: 8 ref: NULL rows: 7050 Extra: Using index*************************** 5. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: ip type: refpossible_keys: POLICY_POLICYHOLDERUUID,IP_PolicyCertificate,endda te_inx key: POLICY_POLICYHOLDERUUID key_len: 9 ref: jgrtest_new.pc.individualPolicyHolderId rows: 2 Extra: Using where5 rows in set (0.01 sec)

    Please give me suggestions how to reduce the execution time.

    Regards
    Susheel
Working...
X