Thanks for recommend

My Table Key is
--------
CA_OTHER_PAYMENT
- alter table ca_other_payment add constraint pk_ca_other_payment
primary key (comp_id, otherpay_code);
- create index ca_other_payment_idx1 on ca_other_payment
(otherpay_date);
- create index ca_other_payment_idx2 on ca_other_payment
(vendor_code);

CA_OTHER_PAYMENT_SUB
- alter table ca_other_payment_sub add constraint
pk_ca_other_payment_sub primary key (comp_id, otherpay_code,
item_no);
- alter table ca_other_payment_sub add constraint
fk_ca_other_payment_sub_1 foreign key (comp_id, otherpay_code)
references ca_other_payment (comp_id, otherpay_code) on update
cascade;
- create index ca_other_payment_sub_idx1 on ca_other_payment_sub
(item_code);

M_ACC_CHART
- alter table m_acc_chart add constraint pk_m_acc_chart primary key
(comp_id, account_code);
- create index m_acc_chart_idx1 on m_acc_chart (account_name);


why add '+0' ?

I'm try change index ca_other_payment_sub_idx1 to
create index ca_other_payment_sub_idx1 on ca_other_payment_sub
(comp_id, item_code);

it slowly.

Thank.

--- In firebird-support@yahoogroups.com, Svein Erling Tysvær
<svein.erling.tysvaer@...> wrote:

>
> I would guess that CA_OTHER_PAYMENT_SUB INDEX

(FK_CA_OTHER_PAYMENT_SUB_1,

> CA_OTHER_PAYMENT_SUB_IDX1) is the problem with query 2. Which

of these are the least selective? Try adding '+0' to the least selective of
CA_OTHER_PAYMENT_SUB.COMP_ID AND
CA_OTHER_PAYMENT_SUB.OTHERPAY_CODE and hopefully your query
will speed up.

>
> Another thing is that if COMP_ID is the primary key of

CA_OTHER_PAYMENT, then you would not want to use any other index
on that table. However, I think Fb 2.0 is intelligent enough to figure this
out by itself, so I guess that you have a composite primary key or
something?

>
> HTH,
> Set
>