Posted by rkershberg@gmail.com on 03/01/06 04:15

I have two tables.

One has approx 90,000 rows with a field .. let's call in BigInt (and it
is defined as a bigint data type).

I have a reference table, with approx 10,000,000 rows. In this
reference table, I have starting_bigint and ending_bigint fields. I
want to pull out all of the reference data from the reference table for
all 90,000 rows in the transaction table where the BigInt from the
transaction table is between the starting_bigint and ending_bigint in
the reference table.

I have the join working now, but it is not as optimized as I would
like. It appears no matter what I do, the query does a full table scan
on the 10,000,000 rows in the reference table.

Sample code

SELECT ref.*, tran.bigint
from transactiontable tran
INNER JOIN referencetable ref on tran.bigint between
ref.starting_bigint and ending_bigint

Yes, all 3 of the fields are indexed. I even have a composite index on
the reference table with the starting_bigint and ending_bigint fields
selected as the composite.

Any help would be appreciated.

Robert H. Kershberg
IT Director
Tax Credit Company
RKershberg@taxcc.com or RKershberg@pobox.com or RKershberg@gmail.com

[Back to original message]