Subject | Re: [firebird-support] Re: Mystery |
---|---|
Author | Svein Erling Tysvaer |
Post date | 2007-06-02T13:10:19Z |
Hi Tom!
Tom Conlon wrote:
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
Tom Conlon wrote:
> Hi Svein,That's the plan I wanted...
>
> 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))
> ------ Performance info ------but not the result I intended. I'll assume it being my Fb 1.5 experience
> Prepare time = 0ms
> Execute time = 5m 25s 500ms (ouch!)
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