OK. Thanks.
The database will be read -only , so the indexes will be mainly to speed up
the query's that run on the database.

At 09:38 AM 3/18/2004, you wrote:

>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
>
>
>
>
>
>Yahoo! Groups Links
>
>
>
>