Cheers,
Ann On Dec 22, 2011, at 4:12 AM, Vander Clock Stephane <[email protected]> wrote: > 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 > > > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > > Also search the knowledgebases at http://www.ibphoenix.com > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Yahoo! Groups Links > > >
