Hi Uwe

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
>
> The table structure looks as follows :
>
> WpIDSQ INTEGER NOT NULL ,
> CityVBSQ INTEGER NOT NULL ,
> WpVBSQ INTEGER ,
> WpName VARCHAR(90) NOT NULL ,
> WpDescription VARCHAR(120) ,
> WpCityName VARCHAR(50) NOT NULL ,
> WpPersonal SMALLINT DEFAULT 1 NOT NULL ,
> WpOrder INTEGER NOT NULL ,
> WpGroup INTEGER NOT NULL ,
> WpLevel INTEGER NOT NULL ,
> CITYNAME VARCHAR(30) NOT NULL ,
> CITYDIALLINGCODE VARCHAR(10) NOT NULL ,
> PRIMARY KEY (WpIDSQ) ,
> FOREIGN KEY (CityVBSQ) REFERENCES City (CityIDSQ) ON DELETE CASCADE ,
> FOREIGN KEY (WpVBSQ) REFERENCES Whitepage (WpIDSQ) ON DELETE CASCADE
>
> 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 ?
>

IMHO putting indexes on WPName, WPDescription, CityVBSQ, CITYNAME and
WPOrder is good. I agree with you. But sometimes putting so much index
could result to a slow update/insert.

I prefer to put one column on index at a time.

Perhaps somebody could tell you which is much better, as Iam a beginner
also.

Cheers,
James