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,enddate_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