Subject INDEX on Numeric Column
Author Robert Zimmermann

I've got a problem with indexing numeric columns on a relatively large amount of (repeatedly requested) data. I'm executing several statements like:

select ((avg(mycol1) + thisandthat) / something) AS myresult
from mytable where
mycol2 >= 10 and mycol2 <= 20 and
mycol3 >= 100 and mycol3 <= 2323 and
mycol4 >= 223 and mycol4 <= 523;

All of those mycolX columns are NUMERIC(10,3). Since table data is increasing steadily (~100.000 rows by now), performance is getting critical. So I tried to speed up things by creating a simple INDEX on these columns, but unfortunately this made no significant difference. I guess it has something to do with floating point values since indexes on integer type fields generally work well. Whats the best way to speed it up?


GMX DSL: Internet, Telefon und Entertainment für nur 19,99 EUR/mtl.!