Hi again!

At 17:23 12.02.2003 +0200, you wrote:

>"select
> pty.party_id, bill.due_date, bill.service_tax
>
>from
> tbl_party pty join tbl_bill bill on (bill.account_id = pty.account_id)
> and bill.service_id = 1
>
>where
> not exists (select * from tbl_invalid inv where inv.party_id =
> pty.party_id)"
>
>All of the fields you see here except "bill.due_date" and
>"bill.service_tax" are indexed fields.
>
>This query without the WHERE clause executes fast (1400 ms). When
>I put the WHERE clause it goes to something like 15 minutes :((
>
>Moreover, in the performance monitor I see only UNINDEXED reads.

I know you've solved this already (finding indexes were turned off), but
there is still a few things that could improve this a bit.

1) Is bill.service_id selective? I.e. does there only exist one or a few
rows in the table with each value? If not, this field should not be indexed.

2) Why do you use "not exists (select * from tbl_invalid inv where
inv.party_id = pty.party_id)"? Change it to "not exists (select 1 from
tbl_invalid inv where inv.party_id = pty.party_id)".

HTH,
Set