MySQL Forums :: General :: Query Performance Problem


Advanced Search

Query Performance Problem
Posted by: Martin Phee ()
Date: September 30, 2009 06:40AM

Having trouble with the query below. It's only happening on one instance of our application. The query will sit there doing a statistics on it and take about 23s. If I remove the where clause it takes 0.03s. I'm not sure what to look at. This has happened on a couple other instances, but after rebuilding the db it seems to work fine then. Also, it isn't just this query but other large ones have the same problem. The query was built by hibernate. I replaced all the columns with a *.

Where running mysql-5.1.36 built from source. Any idea what to look for? I'm at a lose.

show processlist;
| 535 | root | localhost | NULL | Query | 3 | statistics | SELECT *
FROM
smr.cans_assessment this_
LEFT OUTER JOIN smr.users user8_
ON
this_.created_by |

Query:
SELECT *
FROM
smr.cans_assessment this_
LEFT OUTER JOIN smr.users user8_
ON
this_.created_by=user8_.id
LEFT OUTER JOIN smr.users user9_
ON
user8_.created_by=user9_.id
LEFT OUTER JOIN smr.users user10_
ON
user8_.updated_by=user10_.id
LEFT OUTER JOIN smr.users user11_
ON
this_.updated_by=user11_.id
LEFT OUTER JOIN smr.users user12_
ON
this_.approver_id=user12_.id
INNER JOIN smr.users assessor2_
ON
this_.assessor_id=assessor2_.id
INNER JOIN smr.cans_assessment_completion_code cansassess1_
ON
this_.cansAssessmentCompletionCode_id=cansassess1_.id
LEFT OUTER JOIN smr.users user15_
ON
cansassess1_.created_by=user15_.id
LEFT OUTER JOIN smr.users user16_
ON
cansassess1_.updated_by=user16_.id
LEFT OUTER JOIN smr.client caregiver4_
ON
this_.caregiver_id=caregiver4_.id
LEFT OUTER JOIN smr.users user18_
ON
caregiver4_.created_by=user18_.id
LEFT OUTER JOIN smr.users user19_
ON
caregiver4_.updated_by=user19_.id
LEFT OUTER JOIN smr.client_type clienttype20_
ON
caregiver4_.clientType_id=clienttype20_.id
LEFT OUTER JOIN smr.client client21_
ON
caregiver4_.client_id=client21_.id
LEFT OUTER JOIN smr.client client3_
ON
this_.client_id=client3_.id
LEFT OUTER JOIN smr.interview interview23_
ON
this_.interview_id=interview23_.id
LEFT OUTER JOIN smr.users user24_
ON
interview23_.created_by=user24_.id
LEFT OUTER JOIN smr.users user25_
ON
interview23_.updated_by=user25_.id
LEFT OUTER JOIN smr.client client26_
ON
interview23_.client_id=client26_.id
INNER JOIN survey.surveys survey5_
ON
this_.survey_id=survey5_.id
LEFT OUTER JOIN survey.survey_contexts surveycont28_
ON
survey5_.survey_context_id=surveycont28_.id
INNER JOIN survey.instruments instrument6_
ON
survey5_.instrument_id=instrument6_.id
WHERE
instrument6_.base_instrument_type='CANS'
AND client3_.id=2 limit 20

Options: ReplyQuote


Subject Written By Posted
Query Performance Problem Martin Phee 09/30/2009 06:40AM
Re: Query Performance Problem Martin Phee 09/30/2009 06:41AM
Re: Query Performance Problem Martin Phee 09/30/2009 07:09AM
Re: Query Performance Problem Rick James 10/01/2009 08:07PM


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.