Why use both the postcode and primary key indexes for site in your plan?
I suspect you would get far better performance by changing to

WHERE ((s.POSTCODE||'' STARTING WITH 'MK'))

to eliminate the postcode index.

HTH,
Set

Tom Conlon wrote:

> Hi,
>
> 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
> 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" ) )
>
> 169 matches IN *139secs* :(
> (561K skills, 84k clients, 31k sites, 26k companies)
>
> PLAN JOIN (T1 INDEX (FK_CLNTSKILL1),C INDEX (PK_CLIENT),S INDEX
> (IDXSITEPOSTCODE,PK_SITE),CO INDEX (PK_COMPANY))
>
> This query appears to use all indices but first time it executes but
> it takes much longer than it ought.
>
> Once it is in the cache it takes ~2s but that is not much good to the
> end-user plus if you change the postcode to another one it takes more
> time than expected.
>
> Two Questions:
> 1. Any ideas why it takes as long as it does?
> 2. Can I fill the cache ahead of time but issuing a clntskills query
> of some sort that would help with other clntskill requests?
>
> Thanks,
> Tom