> > if FB is giving you the wrong plan, I would suggest your indexes (or
> lack
> > of) are confusing the optimiser.
>
> 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 ?

Unfortunately Firebird does not (yet?) have range statistics (or histograms) on indices (ie: selectivity statistics on ranges instead of the whole index). Maybe using a compound index on (locationid, beds) or (beds, locationid) instead of separate indices on locationid and beds could improve things.
--
Jetzt kostenlos herunterladen: Internet Explorer 8 und Mozilla Firefox 3.5 -
sicherer, schneller und einfacher! http://portal.gmx.net/de/go/chbrowser