--- In firebird-support@yahoogroups.com, Uwe Oeder wrote:

> I have the following query which I need to optimize as it gets
> called a lot.
>
> 'SELECT W.WPOrder , W.CityName || '(' || W.CityDiallingCode || ')'
> AS
> CityName , W.WPIDSQ , W.WPVBSQ , W.WPName , W.WPDescription , W.
> WPLevel , W.CityVBSQ
> FROM Windler W
> WHERE UPPER(W.WPName) LIKE UPPER('%oeder%')
> AND
> UPPER(W.WPDescription) LIKE UPPER('%kwe%')
> AND
> W.CityVBSQ IN (200) ORDER BY 2 , 1

For this query, the only index that can be of any use is on CityVBSQ.
One option that occationally can be useful, is to have separate fields
like UPPER_WPName and UPPER_WPDescription which you index and populate
through triggers. However, such indexes cannot be used in your case,
since you are using '%oeder%' and not 'oeder%' or STARTING 'oeder', i.
e. you do not know the start of the string.

I'm not certain whether Firebird 1.5 has implemented expression
indexes yet, it probably would not help you much since I expect the
result set to be small, but an expression index on [W.CityName || '('
|| W.CityDiallingCode || ')'] could help marginally on the sorting.

Sorry, but indexes are only useful for comparison with the values of
the index itself where the beginning (of strings) or range is known.

HTH,
Set