Yeah the field is indexed. So removing it would help?

I'll try that.
Thanks,
Stephen

----- Original Message -----
From: "Louis Kleiman" <lkleiman@...>
To: <ib-support@yahoogroups.com>
Sent: Friday, June 07, 2002 4:57 PM
Subject: RE: [ib-support] One line of WHERE clause slows query down


> You may have an index on that field. If so, I would probably recommend
> removing it. This index would have very low selectivity due to its low
> number of possible values. Low selectivity means a slow, unhelpful
> index.
>
> Hope this helps.
>
> Louis Kleiman
> SSTMS, Inc.
>
> -----Original Message-----
> From: Stevio [mailto:redeagle@...]
> Sent: Friday, June 07, 2002 11:51 AM
> To: IB Support
> Subject: [ib-support] One line of WHERE clause slows query down
>
> Let me describe my problem with my Firebird database:
>
> Two tables:
> Book (33043 records)
> Borrower (1607 records)
>
> The first query took 21 seconds last time I ran it. The second query
> took
> less than 1 second. For the second query I removed one line that
> accessed a
> location field. This field is a location id field that can have 6
> different
> values (1 to 6). In the book table, 25014 records have a value of 1 for
> this
> field.
>
> Why does this line increase the speed of the query so much? Is there a
> way
> to work around this so that I can still restrict the query to take
> records
> with a value of 1 but without the query taking so long?
>
> QUERY 1 (slow!)
> ------------------
> SELECT
> B.BORROWER_ID,
> BR.NAME
> FROM
> BOOK B,
> BORROWER BR
> WHERE
> B.BORROWER_ID = BR.BORROWER_ID AND
> B.DATE_DUE_BACK IS NOT NULL AND
> B.DATE_DUE_BACK >= '05/10/2002' AND
> B.DATE_DUE_BACK <= '06/07/2002' AND
> B.LOCATION_ID = 1
> GROUP BY
> B.BORROWER_ID,
> BR.NAME
> ORDER BY
> BR.NAME
>
> QUERY 2 (fast! - the blank line is the difference between the two
> queries)
> ------------------
> SELECT
> B.BORROWER_ID,
> BR.NAME
> FROM
> BOOK B,
> BORROWER BR
> WHERE
> B.BORROWER_ID = BR.BORROWER_ID AND
> B.DATE_DUE_BACK IS NOT NULL AND
> B.DATE_DUE_BACK >= '05/10/2002' AND
> B.DATE_DUE_BACK <= '06/07/2002'
>
> GROUP BY
> B.BORROWER_ID,
> BR.NAME
> ORDER BY
> BR.NAME
> ----------------------------
>
> Thanks!
> Stephen