>With Firebird, the choices are simpler, though not better. In the
>absence of the FIRST and ORDER clauses, it will use an index. on
>Price, plus an index on Location, plus an index on Name, plus An
>index on Year. With those clauses, it can use only Creation_Date
>because it will emulate the sort with an index walk, and can
>walk only one index

yes my probleme is the order by :(

actually i MUST specify the PLAN manually

1/ if i thing that location and name will be an enalf filter
INDEX (LOCATION, NAME, CREATION_DATE)
in the way no in memory order by. the filter on the other field
price, year, etc... will be done manually row by row

WHERE LOCATION = xxx and NAME = yyy
ORDER BY LOCATION, NAME, CREATION_DATE


2/ if the location and name are too much open wide filter
(return too much of rows) then i use one of the most acurate :

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

and the order by will be done in memory on the returned result

the probleme is that is a far away perfect solution

1/ how to know with index to use (actually i bench each query and
remembered the
bench result to know if the previous query was acurate or not)

2/ i alway need to choose between an index based on the order by
or based on the criteria ... but sometime it's not possible to have good
result


but this situation is very common i thing (for anyone that need the
order by) this
why i m curious how other face such probleme

stéphane

On 12/26/2011 11:22 AM, Ann Harrison wrote:
>
>
> Stephane,
>
> >
> > 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) ?
>


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