--- In firebird-support@yahoogroups.com, "Epstein, Ed" wrote:

> I have 2 different tables with a double precision field in each one.
> Both are indexed. I am trying to perform a query of the values that
> are in both tables.
>
> I have tried many variations, but this is the most logical one to
> me:
>
> SELECT * FROM TABLEA JOIN TABLEB ON TABLEA.FIELD1 = TABLEB.FIELD1
>
> The Plan that is generated for that is
> (TableA Natural,TableB (Index TableB_Field1_A))
>
> Wouldn't the best plan be to use both indexes?

No, Firebird/Interbase needs a where clause to be able to use an index
on the first table in the plan. Basically, the question is: "Which
records shall I check in TableA? The answer is 'all of them'. The next
question is, what shall I check for in TableB, with the answer
'records that have the same value of Field1 as in TableA. It is the
best plan you could possibly get for this select.

> Basically I am trying to create the fastest query to return values
> from both tables that are the same. So far it takes at least 2-5
> minutes which is not very good.

Does that mean there are lots of records returned and that the tables
are huge? Or just lots of duplicates for Field1? Any other bottlenecks
(RAM, slow lines etc.)?

Set