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



To unsubscribe from this group, send an email to:
ib-support-unsubscribe@egroups.com



Your use of Yahoo! Groups is subject to
http://docs.yahoo.com/info/terms/