Hi!

I have a performance problem running a query with firebird 1.51. I
hope someone can help me!

The following SELECT-statement reads ALL records from BOTH tables
(monitored with IBExpert) while the result set has only a few
records. This results in a rather slow execution time!!

SELECT *
FROM Table1
JOIN Table2 ON Table2.RefID=Table1.ID
WHERE ((Table1.Status=1) OR (Table1.Status=3))

IBExpert reports the following plan:
PLAN JOIN (Table1 NATURAL,Table2 INDEX (RDB$FOREIGN82))

The WHERE-condition meets with only 10 records, so the result-set is
only 10 records.
Table1 and Table2 contain both more then 10.000 records.
Table2 has a foreign key (RefID) on Table1(ID), the plan seems to be
correct.

In my opinion there is no need to read all records from Table2 for
the JOIN ?!
Without the OR statement (Where-clause: "WHERE (Table1.Status=1)")
the records are really not read.

With Interbase 5.6 I can run the query about 50times faster !!!!
(Interbase 5.6 does not retrieve all the data from the second
table!!)

Is IB5.6 better than firebird 1.51 ?
Would you recommend to use firebird 1.03 ?
Is there something wrong with my firebird-setup?
Is this a "bug" in firebird 1.51?

Thanks,
Martin