Robert Zimmermann wrote:

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

First, numeric(10,3) is stored as an integer. If the value stored
111.999, the stored representation is that times 10**3 or 111999.

Second, all numeric values except bigint are converted to double
precision floating point when they're included in an index key,
so all your keys have always been double precision.

Did you create one index on all three fields, or did you create
one index per field? If you've got a compound index, only an
equality match on the first field will let Firebird use the
second field. So an index on mycol2, mycol3, mycol4, only the
range retrieval on mycol2 will use the index. If you have separate
indexes on the three, Firebird will find all the matches on the
first index and store the record numbers in a sparse bitmap, then
do the same lookup on the second index, and the third, then combine
the bitmaps with an AND operation, so you get full benefit.


Good luck,

Ann