> select fieldA, fieldB from table1
> where (fieldA starting with 'bd'
> or fieldA starting with 'bi')
> and fieldA between 'bd' and 'bj')
> order by fieldA asc, fieldC asc
>
> Thanks, will try that out and report back.
> I started to use the union instead of the OR when we
> were still on Interbase and the difference in speed then was enormous. We then
> moved to Firebird and the difference was still there.
> Note that we are still on Firebird 1.5 if that makes a difference.

Could be, because the V2 series offers a lot of improvements in the
optimizer area.

If possible, I would seriously try that with Firebird 2.5.

--
With regards,
Thomas Steinmaurer
http://www.upscene.com/

> RBS
>
>
>
> On 7/16/12, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:
>>> Firebird 1.5 classic on Windows.
>>> Is it possible to have a SQL with union all and an order by that includes a
>>> field that is not in the selects?
>>
>> I'd be very surprised if this was possible, logically you sort a result set
>> and the result set that does not contain your field. If it wasn't for the
>> union, it is at least thinkable (for people like me without knowledge of
>> Firebird internals) that the sorting could be done during the selection of
>> records and that it could sort on the field. Though it should be simple for
>> you to test to find out whether it is possible or not.
>>
>>> In case you wondered why I used a union here, it is as I found it be a lot
>>> faster than using an OR.
>>
>> That must either be coincidental or because the PLAN changes. My guess is
>> that your selectivity indicates that using an index is marginally better
>> than using NATURAL, but that this changes once you introduce OR (it isn't
>> generally quicker to use UNION than OR). Since Firebird doesn't have
>> histograms yet (it doesn't know whether 0.001% or 99% starts with 'bd' or
>> 'bi'), this may be a wrong choice.
>>
>> If my guess is correct, then the following might make your query quick
>> again:
>>
>> select fieldA, fieldB from table1
>> where (fieldA starting with 'bd'
>> or fieldA starting with 'bi')
>> and fieldA between 'bd' and 'bj')
>> order by fieldA asc, fieldC asc
>>
>> HTH,
>> 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
>
>
>