nested select

[meta mind]

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.