MySQL Forums :: Newbie :: nested select


Advanced Search

nested select
Posted by: meta mind ()
Date: February 22, 2010 08:37AM

Hi,

I have three 4 tables,

- vPamSelectionTest
- vPamSelectionTestCandidate
- vPamVacancyCandidate
- vPamVacancy

I am looking at a vacancy and trying to pull out the details of every selectionTest that is (linked to that vacancy) or (linked to one of the candidates that is allocated to that vacancy). My sql looks like this (it is inside a stored proc so the _vacancyId is being passed in):

select
st.`selectionTestId`,
st.`vacancyId`,
st.`consultantId`,
st.`consultantName`,
st.`stDate`,
st.`location`,
st.`otherDetails`,
st.`stStatus`,
(select count(*) from vPamSelectionTestCandidate stc where stc.selectionTestId = st.selectionTestId and stc.stCaStatus in ('p','c','y')) as numCandidates
from `vPamSelectionTest` st
where
st.stStatus <> 'v'
and st.`selectionTestId` in (
select distinct(selectionTestId) from vPamSelectionTestCandidate where candidateId in (
select candidateId from vPamVacancyCandidate where vacancyId = _vacancyId
)
union
select selectionTestId from vPamSelectionTest where vacancyId = _vacancyid
)
;


As you can see I have nested selects building up a list of the selectionTestIds that I should pull out. The bit that is running like a hobbled pig is the nested select. When I run:

select distinct(selectionTestId) from vPamSelectionTestCandidate where candidateId in (
select candidateId from vPamVacancyCandidate where vacancyId = _vacancyId

It comes back quickly with 96. When I replace that part of the original select with 96 it runs great. The original select is taking about 30 secs to run though.

I profiled the select, when I ran " show profile for query 1" I got 85181 rows back (sample below:)

| Sending data | 0.000315 |
| executing | 0.000001 |
| Sending data | 0.000313 |
| executing | 0.000001 |
| Sending data | 0.000313 |
| executing | 0.000001 |
| Sending data | 0.000315 |
| executing | 0.000001 |
| Sending data | 0.000313 |
| executing | 0.000001 |
| Sending data | 0.000315 |
| executing | 0.000001 |
| Sending data | 0.000316 |
| executing | 0.000000 |
| Sending data | 0.000314 |
| executing | 0.000001 |
| Sending data | 0.000313 |
| executing | 0.000001 |
| Sending data | 0.000315 |
| executing | 0.000001 |
| Sending data | 0.000313 |
| executing | 0.000000 |
| Sending data | 0.000314 |
| Sending data | 0.000001 |
| executing | 0.000000 |
| Sending data | 0.000006 |
| executing | 0.000000 |
| Sending data | 0.000014 |
| end | 0.000003 |
| removing tmp table | 0.000004 |
| end | 0.000001 |
| removing tmp table | 0.000002 |
| end | 0.000005 |
| query end | 0.000001 |
| freeing items | 0.000036 |
| logging slow query | 0.000001 |
| logging slow query | 0.000001 |
| cleaning up | 0.000003 |
+----------------------+----------+
85181 rows in set (0.24 sec)

Any ideas?

Thanks



Edited 2 time(s). Last edit at 02/22/2010 08:47AM by meta mind.

Options: ReplyQuote


Subject Written By Posted
nested select meta mind 02/22/2010 08:37AM
Re: nested select laptop alias 02/22/2010 09:19AM
Re: nested select meta mind 02/22/2010 11:07AM
Re: nested select Rick James 02/25/2010 04:50PM


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.