Hmm, Tom, mystery sounds like a fair description...

I have no clue why it is so slow. I think I once had a problem with
something being too slow (selecting from a tiny table or using very
selective criteria took ages) that I concluded had to be due to some
error in my database. Have you tried a backup/restore and run your query
against the restored database?

Another thing you could try, is to force using NATURAL on Vacancy, and
then the primary key on the other tables:

SELECT c.clntid, c.surname, c.forename, c.title, c.siteid, v.vacid,
v.jobtitle, v.vactype, v.rate, v.isactive, co.compname, c.email
FROM Vacancy v
JOIN Client c ON c.clntid=v.clntid+0
JOIN Site s ON c.siteid+0=s.siteid
JOIN Company co ON s.compid+0=co.compid
WHERE s.REGIONID+0 BETWEEN 1 and 4

Though I'm sorry to say that I doubt it will help much (based on the
fact that I think your original plan looks OK - that is, unless REGIONID
1-4 has lousy selectivity),
Set

tomconlon7777777 wrote:

> Hi,
>
> The query below seems to use all available indexes but the
> performance is bizarrely slow (v1.53). It stood out as it took 3mins
> at a client site so then it was ran on a dev machine. If anyone has
> any ideas/suggestions it would be appreciated.
>
> Thanks,
> Tom
>
> -----------------------------------------------------------
> SELECT c.clntid, c.surname, c.forename, c.title, c.siteid, v.vacid,
> v.jobtitle, v.vactype, v.rate, v.isactive, co.compname, c.email
> FROM Vacancy v
> JOIN Client c ON c.clntid=v.clntid
> JOIN Site s ON c.siteid=s.siteid
> JOIN Company co ON s.compid=co.compid
> WHERE ( ( s.REGIONID IN(3,1,2,4) ) )
>
> PLAN JOIN (S INDEX
> (IDXSITE_REGIONID,IDXSITE_REGIONID,IDXSITE_REGIONID,IDXSITE_REGIONID),CO
> INDEX (PK_COMPANY),C INDEX (FK1_CLIENT),V INDEX (FK1_VACANCY))
>
> ------ Performance info ------
> Prepare time = 0ms
> Execute time = 8m 28s 594ms (!)
>
> Company count: 32484
> Site count: 38837
> Client count: 117194
> Vacancy count: 21755
>
> /*** Primary Keys
> ALTER TABLE CLIENT ADD CONSTRAINT PK_CLIENT PRIMARY KEY (CLNTID);
> ALTER TABLE COMPANY ADD CONSTRAINT PK_COMPANY PRIMARY KEY (COMPID);
> ALTER TABLE SITE ADD CONSTRAINT PK_SITE PRIMARY KEY (SITEID);
> ALTER TABLE VACANCY ADD CONSTRAINT PK_VACANCY PRIMARY KEY (VACID);
>
>
> /*** Foreign Keys
> ALTER TABLE CLIENT ADD CONSTRAINT FK1_CLIENT FOREIGN KEY (SITEID)
> REFERENCES SITE (SITEID);
> ALTER TABLE CLIENT ADD FOREIGN KEY (STAFFID) REFERENCES STAFF (STAFFID);
> ALTER TABLE COMPANY ADD FOREIGN KEY (SICID) REFERENCES SIC (SICID);
> ALTER TABLE SITE ADD CONSTRAINT FK1_SITE FOREIGN KEY (COMPID)
> REFERENCES COMPANY (COMPID);
> ALTER TABLE VACANCY ADD CONSTRAINT FK1_VACANCY FOREIGN KEY (CLNTID)
> REFERENCES CLIENT (CLNTID);
> ALTER TABLE VACANCY ADD CONSTRAINT FK2_VACANCY FOREIGN KEY (STAFFID)
> REFERENCES STAFF (STAFFID);
>
>
> /*** Indices
> CREATE INDEX IDXSITE_REGIONID ON SITE (REGIONID);