On Dec 22, 2011, at 4:12 AM, Vander Clock Stephane <svanderclock@...> 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
>
>
>