Hi,

> 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
>
> Having read about PLAN's I am now not totally sure on which fields to
> generate index's and how many index's to generate. I am planning to make

an

> index with : CITYNAME and WPOrder as they are used in the sort order. But

I

> will probably also need indexes for WPName , WPDescription , CityVBSQ as
> they are used in the searches. Now should I combine the three fields in

one

> index or make three seperate index knowing that I do not alway search on
> all 3 fields at the same time , but it will always be a combination of

only

> these tree fields ?

For this query a index on CityVBSQ is the only one that will actually help.
Because you use LIKE with '%' at the beginning no index can be used for
WPName and WPDescription. Also if you use UPPER then also no index can be
used.
Instead of UPPER(Fieldname) LIKE UPPER('%sometext') you could use
CONTAINING, but still no index can be used here.
Using a index on WPOrder for the ORDER BY clause is not possible here,
because you've also a concatenation were you are sorting on. Even if you
were sorting only on WPOrder it's probably slower with index then without.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81