On 12/26/2011 11:11 AM, Ann Harrison wrote:
>
>
>
> Cheers,
>
> Ann
>
> On Dec 22, 2011, at 4:12 AM, Vander Clock Stephane
> <svanderclock@... <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]