Hi

> 8 000 000 records
> fieldA = Price (between 0 to 1000000, mostly around 250000)
> fieldb = Size (between 0 to 200 mostly around 60)

If you have that much information, you could write a stored procedure, like
the following pseudo code:

If ( ( a_max - a_min ) / 1000000.0 < ( b_max - b_min ) * 200.0 ) then begin
select * from xzy where a >= a_min and a <= a_max and b + 0 >= b_min and b
+ 0 <= b_max into .... suspend....
else
select * from xzy where a + 0 >= a_min and a + 0 <= a_max and b >= b_min
and b <= b_max into .... suspend....
end

You could adapt this to even use both indexes, if both are more or less
equally selective (given the input) and you could drop one of the clauses,
if any value matches (for example a_min = 0), or you could inspect, if one
of the searched ranges is at the average or not, and much more.
The basic advantage would be that you can redefine that stored procedure
later on and thereby improve your databases speed upon usage statistics
without changing you applications code.

Regards,
Steffen



[Non-text portions of this message have been removed]