On Sat, Feb 16, 2013 at 5:37 PM, ml600f wrote:
>
> i often need ranges in my program. Searching for bottlenecks i see that
> BETWEEN is sometimes
> very slow.
>
Use a separate index for each column that you use in a range query.
Firebird can combine ranges from separate indexes but cannot use
>secondary index on ID,ADATE,NUMBER
>
>First example (slow)
>
>SELECT * FROM table
>WHERE (ID BETWEEN 1000 AND 1000)
> AND (ADATE BETWEEN '01.01.2013' AND '01.01.2013')
>ORDER BY ID,ADATE,NUMBER
>Elapsed time = 9 seconds resultset 8 records.
>Plan shows firebird use correct index INDEX ID,ADATE,
Hello Ann,
thank you for your good explanation.
Now it´s clear to me what happens.
I´ve created seperate indices on all three fields. It´s much quicker <
1second but not really quick.
In my example there are ~7000 records for the given id and ~2000 for the
given date. Firebird must
calculate the
