Or you could have a CustomerCode table listing all possible codes, and
SalesOrder.CustomerCode and Customer.CustomerCode would both have
foreign keys pointing to the CustomerCode table.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of Ann W. Harrison
Sent: Wednesday, June 21, 2006 4:59 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: How to create index for field in two
tables and had any limitation

> <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


------------------------ Yahoo! Groups Sponsor --------------------~-->
Check out the new improvements in Yahoo! Groups email.
http://us.click.yahoo.com/6pRQfA/fOaOAA/yQLSAA/67folB/TM
--------------------------------------------------------------------~->

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://firebird.sourceforge.net and click the Resources item on
the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Yahoo! Groups Links