I would consider restructuring the query to make the most unique index
select first and also put some of the where clause into the joins instead
of the result set. To do this you need to know how many records from each
table, each clause would select.

_____

From: Alexander Tabakov [mailto:saho@...]
Sent: Friday, December 12, 2003 15:19
To: Svein Erling
Subject: Re: [firebird-support] Re: Index Question



Hello Svein,

>I think we need more information to answer this one, i.e. sql
>statement, chosen plan and

The query:

select ...
from
TBL_PARTY p join TBL_PAYMENT pmnt on (pmnt.PARTY_ID = p.PARTY_ID)
join tbl_bill_payed bill on (pmnt.pmnt_id = bill.pmnt_id)
where
(pmnt.BILLING_DATE BETWEEN ... AND ...) and bill.service_id =
:serviceId

Query plan:

PLAN JOIN (PMNT ORDER IDX_PAYMENT_DATE,P INDEX (RDB$PRIMARY26),BILL INDEX
(TBL_BILL_PAYED_IDX2));

Where:
IDX_PAYMENT_DATE - index on payment date
RDB$PRIMARY26 - PK index on TBL_PARTY (party_id)
TBL_BILL_PAYED_IDX2 - The index in question! It was either
(payment_id,service_id) or (service_id,payment_id)

>information about how many duplicates/matching records are expected for
>PAYMENT_ID and SERVICE_ID.

The query normally selects about 1000 records of 100000

--
Best regards,
Alexander mailto:saho@...






Yahoo! Groups Sponsor



ADVERTISEMENT

<http://rd.yahoo.com/SIG=12co0483a/M=258297.4271147.5470572.4049140/D=egroup
web/S=1705115386:HM/EXP=1071321572/A=1683962/R=0/*http:/www.techsmith.com/rd
r/ban/syah/default.asp> click here



<http://us.adserver.yahoo.com/l?M=258297.4271147.5470572.4049140/D=egroupmai
l/S=:HM/A=1683962/rand=552230715>


To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com



Your use of Yahoo! Groups is subject to the Yahoo!
<http://docs.yahoo.com/info/terms/> Terms of Service.



[Non-text portions of this message have been removed]