Hello,

I have a problem that i thing any web site can have !

Let say i m a car dealer WebSite and want to permit user to search my
zillions car Firebrd database

Typycal search will be

SELECT
FIRST 100 SKIP 0,
*
FOR
CAR
Where
NAME = 'BMW' AND
LOCATION = 'NEW YORK CITY' AND
PRICE > 30000 AND
PRICE < 40000 AND
YEAR > 2004
ORDER BY CREATION_DATE DESC


So here what index strategy to choose ?

INDEX (LOCATION, NAME, PRICE) ?
INDEX (LOCATION, NAME, YEAR) ?
INDEX (LOCATION, NAME, CREATION_DATE) ?
INDEX (PRICE) ?
INDEX (YEAR) ?
INDEX (CREATION_DATE) ?

because all of this depend on how many row will be returned !

for exemple if many many rows are returned by the filter then it's will
be better
to retrieve the row in the ORDER BY and filter them row by row :

SELECT
...
ORDER BY NAME, LOCATION, CREATION_DATE DESC

INDEX (LOCATION, NAME, CREATION_DATE) ?

but in other way i only few row are returned by the query then it's will
be better
retrieve the row already filtered and order them in memory :

SELECT
..
ORDER BY CREATION_DATE DESC

using the most(S) usefull index :
INDEX (LOCATION, NAME, PRICE) ?
INDEX (LOCATION, NAME, YEAR) ?
INDEX PRICE
INDEX YEAR

but here also how to know with index will be usefull ??
sometime price sometime year, sometime both ...


How do you handle such situation ? here the biggest probleme is
of course the need of the order by


thanks you by advance
stéphane