Thanks Set, I'll try this on one of the SP's that I have already converted to ranges, and have large volumes of data for, and am now experiencing problems with, and see how it goes.

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: 19 May 2008 01:28 PM
To: 'firebird-support@yahoogroups.com'
Subject: [firebird-support] Re: Possible to write this in a way that indices will be used

>>Set wrote:
>>However, if I specify the plan:
>>
>>Select <whatever>
>>From tablea a
>>Where a.VerySelectiveField between :fromvalue1 and :tovalue1
>> And a.AlmostAsSelectiveField between :fromvalue2 and :tovalue2
>>PLAN (A INDEX(IndexVerySelectiveField, IndexAlmostAsSelectiveField))
>>
>>then the performance is decent in both cases, although not as excellent
>>as if the best plan had been chosen depending >on the parameters.
>
>Sounds good. To help me understand better, what exactly am I telling FB
>to do here though? Use both indexes simultaneously?

Yes, you're telling it to use both indexes. With one parameter being selective and the other one including all values, one of them will speed up execution and the other one slow it down. I have no idea how selective the selective part has to be for this to be better than going NATURAL, but from my visual inspection it at least seemed to be noticeably better to use both indexes (compared to using none) when one of the criteria selected one out of a million records in Firebird 1.5.4. If the most selective criteria selects a much higher proportion of the records (e.g. 10%), then NATURAL may be better - I simply don't know.

Set

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links