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