This clause

> WHERE UPPER(W.WPName) LIKE UPPER('%oeder%')
> AND
> UPPER(W.WPDescription) LIKE UPPER('%kwe%')
> AND
> W.CityVBSQ IN (200) ORDER BY 2 , 1

... means that WPName and WPDescription are of little use for performance tuning. WPOrder is nopt part of the search criteria so its index will not get used.

However, an index on CityVBSQ would be very useful.

I would empirically test a compound index on CityVBSQ, WPName, and WPDescription.

I would also look into standardizing case so you don't have to call the UPPER function twice on every row you view. If you need mixed case output, I would consider separating display field from search fields, and put the search fields into the index.

----- Original Message -----
From: Arno Brinkman
To: firebird-support@yahoogroups.com
Sent: Thursday, March 18, 2004 12:13 AM
Subject: Re: [firebird-support] INDEXes for a Query


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



------------------------------------------------------------------------------
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]