At 09:58 AM 09-08-02 +1000, you wrote:

>Hi,
>
>We have a large table, currently >400,000 records of 34 fields. Many of our
>queries involve date ranges, e.g. report on all the work for first week in
>july:
>
>SELECT * FROM SAWMEMBERSARCHIVED
>WHERE START_TIME >= '7/1/2002' AND START_TIME < '7/6/2002'
>
>It returns 1283 records. I ran it a few times with and without an index.
>I created the index with this simple statement:
>CREATE INDEX SMAJOB ON SAWMEMBERSARCHIVED (START_TIME)
>
>Typical time without index: 4 secs
>Typical time with index: 4 secs.

On 400,000 rows, if the optimizer used the index, you would see a dramatic
difference. Thus it's unlikely the optimizer is choosing to use the
index. Get hold of a tool such as IB_SQL that shows you the plan after the
query is prepared and see what's really going on. After that, you can
start hacking the query to force use of your index.

heLen

All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
______________________________________________________________________