> <jeremylim2008@...> wrote:
>
>> I am using firebird version 1.5, Delphi 7, bde and try
>> to open tquery with below sql statement.
>>
>>"Select * From SalesOrder
>> Left Join Customer on C.CustomerCode = S.CustomerCode
>> order by customername "
>>
>>the customer records got 100, 000 records and sales order got
>>around one million records.
>>It took more than 2 minutes to open the query. as i know indexing
>>will improve a lots the speed but indexing for 2 table I never try before.
>>
>

Adam wrote:

>
>
> Indexing wont help a lot because you don't use a where clause on a
> very large table.

Adam is generally very knowledgeable about firebird, but he's having
a bad day. Adding an index on the CustomerCode field in Customers
will improve performance a lot. You're currently reading a million
sales records and fore each sales record, you're reading 100,000
customer records, for a total of 100,000,000,000 record reads. If
you had an index on CustomerCode in Customer (that's the only field
that will work) - you'd still read all 1,000,000 SalesOrder records
but for each, you'd only look at three index pages (of which two are
nearly certain to be in cache) and one Customer record. It's a huge
win.

>

Regards,


Ann