At 08:22 AM 18/03/2004 +0200, you 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 , /* too
slow for ordering */
W.WPIDSQ ,
W.WPVBSQ ,
W.WPName ,
W.WPDescription ,
W.WPLevel ,
W.CityVBSQ
FROM Windler W
WHERE
UPPER(W.WPName) LIKE UPPER('%oeder%') /* can't use index */
AND
UPPER(W.WPDescription) LIKE UPPER('%kwe%') /* can't use index */
AND
W.CityVBSQ IN (200) /* invalid */
ORDER BY 2 , /* can't use an index */
1 /* can use an index */

>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.

Not in your query. You are not sorting on CityName. Yes to WPOrder.

>But I
>will probably also need indexes for

>WPName , - not used

>WPDescription , - not used

>CityVBSQ - will be used if you correct the search clause


>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 ?

Those UPPER() and LIKE comparisons will make this a slow query.
At least you can do 3 things to enable index use for part of the search and
for the orderings.

Change the query to:

'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%') /* can't use index */
AND
UPPER(W.WPDescription) LIKE UPPER('%kwe%') /* can't use index */
AND
W.CityVBSQ = 200 /* will use an index but no good if selectivity is bad */
ORDER BY W.CityName,
W.CityDiallingCode,
W.WPOrder

Make an index on (CityName, CityDiallingCode).

/heLen