MySQL Forums
Forum List  »  Optimizer & Parser

Please help to optimize slow running query
Posted by: sus moh
Date: March 07, 2008 12:40AM

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 and
pc.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: ref
possible_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_ref
possible_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_ref
possible_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: index
possible_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: ref
possible_keys: POLICY_POLICYHOLDERUUID,IP_PolicyCertificate,enddate_inx
key: POLICY_POLICYHOLDERUUID
key_len: 9
ref: jgrtest_new.pc.individualPolicyHolderId
rows: 2
Extra: Using where
5 rows in set (0.01 sec)

Table Name : No Of Rows
CreditCard : 1799
IndividualPolicyHolder : 1746
PolicyCertificate : 726
IndividualPolicy : 7050

Please give me suggestions how to reduce the execution time.

Regards
Susheel

Options: ReplyQuote


Subject
Views
Written By
Posted
Please help to optimize slow running query
3617
March 07, 2008 12:40AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.