Hi Tom!

Tom Conlon wrote:

> Hi Svein,
>
> Thanks for you answer.
>
>> I suspect you would get far better performance by changing to
>> WHERE ((s.POSTCODE||'' STARTING WITH 'MK'))
>
> Unfortunately Not:
>
> PLAN JOIN (T1 INDEX (FK_CLNTSKILL1),C INDEX (PK_CLIENT),S INDEX
> (PK_SITE),CO INDEX (PK_COMPANY))

That's the plan I wanted...

> ------ Performance info ------
> Prepare time = 0ms
> Execute time = 5m 25s 500ms (ouch!)

but not the result I intended. I'll assume it being my Fb 1.5 experience
that isn't applicable to your problem (I normally find additional
indexes to be time consuming if the PK is already used for a table in
the plan). It is still possible to try to force a different plan, but I
do not expect it to be any better (after all, the optimizer chose your
current plan):

SELECT c.clntid, c.surname, c.forename, c.title, c.siteid, c.email ,
co.compname
FROM Client c
JOIN Site s ON c.siteid=s.siteid+0
JOIN Company co ON s.compid=co.compid
JOIN clntskill t1 ON (c.clntid=t1.clntid) AND (t1.skillid=34 )
WHERE s.POSTCODE STARTING WITH 'MK'

I think this could help if skillid 34 is very common, whereas postcodes
starting with MK are rare. If not, I'm afraid that I have no further
suggestions (well, it would be interesting to see the plan anyway to see
if that says something interesting). The plan I hope to get is something
like

PLAN JOIN (S INDEX(IDXSITEPOSTCODE), C INDEX (IDXSITE_ID), T1 INDEX
(IDXCLNTID), CO INDEX (PK_COMPANY))

(and then FK_CLNTSKILL1 may possibly be added to T1)

HTH,
Set