dear ann,

yes, it's a brand new generated table for my test :)

but anywway, i don't see how firebird can gave me the good plan ! the only way do do that for him will be to calculate first the count and after he can know with index to use... as i say
select * from property where locationid = 'xxx' and beds > 1 order by date desc
and
select x from property where locationid = 'xxx' and beds > 100 order by date desc

in the first case, we understand that we don't need to use the index on beds (mostly all house have more than one beds), but in the second case we imediatly understand that we need to use the index on beds (very very few house have more than 100 beds). but even we also need to know how many property are in 'xxx', cause if it's a little city with few property, then we don't need to use any other index than the index on locationid, date (for the order by) ...

the problem is simple: using an index can drastically get down (or get up) the performance but it's depend of the SQL params !

now how to do to alway choose the good index in the plan ?

thanks you by advance
stephane

--- In firebird-support@yahoogroups.com, "Ann W. Harrison" <aharrison@...> wrote:
>
> Alan McDonald wrote:
> >>
> >> i m facing a problem with a easy thing.
> >> i have a table with 10 000 000 rows
> >>
> >> Table Property
> >> Date timestamp;
> ...
> >>
> >> all columns have an index on it.
>
> >
> > you use a lot of > AND <
> > do you have desc as well as asc indexes on these fields?
>
> And have you updated the index statistics lately? Firebird does
> not do that automatically.
>
>
> Good luck,
>
> Ann
>