not sure i understand :) On 12/26/2011 11:11 AM, Ann Harrison wrote: > > > > Cheers, > > Ann > > On Dec 22, 2011, at 4:12 AM, Vander Clock Stephane > <[email protected] <mailto:svanderclock%40yahoo.fr>> 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 > > > > > > > >
[Non-text portions of this message have been removed]
