Subject Re: need your advise to optimize an easy query
Author svanderclock
> do you have desc as well as asc indexes on these fields?


> (I don't see a primary key - why not?)

because it's just a special table build by trigger to handle listing search. the original table is much more complicated

> 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
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 ?

thanks you by advance