> 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.tysv...@kreftregisteret.no> > 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 > > >