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