dear Arno,

> Could LocalisationID not be an INT (or BIGINT) ?

yes, could be

> Do i understand correctly that LocalisationID is always part of the >query?

yes, always

>So yes, then an DESC index on
> (LocalisationID, Date) seems very usefull to start with

yes, this what we do actually, i write badly in my exemple order by date desc, it was in fact order by localisationID desc, Date desc

> Looking at what your data contains i think that filtering on "Beds" a "Baths" is not usefull unless they contain a very
> high number (thus which is rarely used), so something to keep in mind. Further only if the price-range is very small or
> very high the price is interesting to use too, so may be you can do something with that information.

yes, in 95% of the case, we don't need index on such field, but in the 5% we strongly need (or query can take > 30 seconds to return the result)

> You can also build a your own cache that stores the best PLAN by a query and run a background thread that finds once in
> a time period the fastet one to use, but i would always prefer to use as much as possible the PLAN generated by Firebird
> itself.

this my idea, use a table for statistic and store in it for each query the best plan to use. if a query take more than 200 ms to execute, then investigate to found the best plan. yes we will need to do 2 more Select for every select but at the end it's can save more

stephane

--- In firebird-support@yahoogroups.com, Arno Brinkman <fbsupport@...> wrote:
>
> Hi,
>
> > i m facing a problem with a easy thing.
> > i have a table with 10 000 000 rows
>
> > Table Property
> > Date timestamp;
> > LocalisationID Varchar(100);
> > Price NUMERIC(15,5);
> > Beds SMALLINT
> > Baths SMALLINT
> > SQFT NUMERIC(12,4)
>
> Could LocalisationID not be an INT (or BIGINT) ?
>
> Do i understand correctly that LocalisationID is always part of the query? So yes, then an DESC index on
> (LocalisationID, Date) seems very usefull to start with and this query:
>
> SELECT
> FIRST 200 *
> FROM
> Property p
> WHERE
> p.LocalisationID = 'xxx'
> /* and p.Price >= ..... and p.Price <= .... and whatever */
> ORDER BY
> p.LocalisationID DESC, p.Date DESC
>
> Looking at what your data contains i think that filtering on "Beds" a "Baths" is not usefull unless they contain a very
> high number (thus which is rarely used), so something to keep in mind. Further only if the price-range is very small or
> very high the price is interesting to use too, so may be you can do something with that information.
>
> You can also build a your own cache that stores the best PLAN by a query and run a background thread that finds once in
> a time period the fastet one to use, but i would always prefer to use as much as possible the PLAN generated by Firebird
> itself.
>
> Regards,
> Arno Brinkman
> ABVisie
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> Firebird open source database with many powerful SQL features:
> http://www.firebirdsql.org
> http://www.firebirdsql.info
>
> General database developer support:
> http://www.databasedevelopmentforum.com
>
> Support list for Interbase and Firebird users:
> firebird-support@yahoogroups.com
>
> Nederlandse firebird nieuwsgroep:
> news://newsgroups.firebirdsql.info
>