> hello,
>
> 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)
>
> all columns have an index on it.
>
> to gave you an idea, you can query this database from
> http://www.trulia.com/
>
> all the result need to be ordered by TimeStamps. we need only the 200
> first records, and max 100 ms by query
>
> now when i play myself with the plan i can always found a way to
> retrieve the result in < 100 ms. but my probleme is to do it
> automatiquely
>
> for exemple
>
> select * from Property where LocalisationID = 'xxx' and price > 100000
> and price < 1500000 and beds > 3 order by Date desc
>
> their is more than one plan we can choose (and before to try i can not
> really say with will be the best) :
>
> Plan (PROPERTY ORDER index_LocalisationID_date);
> Plan (PROPERTY ORDER index_LocalisationID_date INDEX (index_price));
> Plan (PROPERTY ORDER index_LocalisationID_date INDEX (index_beds));
> Plan (PROPERTY ORDER index_LocalisationID_date INDEX (index_price,
> index_beds));
>
> or other usefull plan i forget ?
>
> with index_LocalisationID_date = index on LocalisationID, date desc
>
> now the problem is with plan to choose ?? (ie: not let firebird choose
> himself, he will most of the time choose the wrong)
>
> if lot of property in the location 'xxx' have price > 100000 and price
> < 1500000 then using INDEX (index_price) is bad, and slow the query (by
> 5 times i see). in a opposite way if only few property have a price in
> this range then using the index index_price is very good ! this is same
> problem for every colum (baths, sqft, beds, etc...)
>
> the automatic thing will be to do before a select count(x) from
> Property where price > 100000 and price < 1500000 and LocalisationID =
> 'xxx' to know if it's will be good to use or not the index! but i can
> not do this because the select count is really to much slow !
>
> So how you will do ? i take any opinion/idea ...
>
> thanks you by advance
> stephane
>

you use a lot of > AND <
do you have desc as well as asc indexes on these fields?
(I don't see a primary key - why not?)
if FB is giving you the wrong plan, I would suggest your indexes (or lack
of) are confusing the optimiser.
Alan