2458 comms doesn't sound too bad for a query. Whether it is good enough I would guess would depend on whether this database is heavily used or not - your queries seem fine to me, but you may work in a completely different setting than I do. I do understand that you use LEFT JOIN, at least if you're also interested in incidents for persons without a record in customercommunications.

I agree that Firebird 2 may not change the index use for this particular query, I think the optimizer was further optimized before 2.0, but it may not change optimal plans. The benefits from changing to 2.0 may be a small speed increase, though I don't expect a great benefit for this query. My earlier suggestion used an added feature for Firebird 2.1, something that I think may be a better reason to upgrade. IIRC this should also be possible in Fb 2.0 through using derived tables, but I can be wrong about what appeared in which version.

However, you're still on Fb 1.5 and your queries seem quite nice. Are there lots of users that are going to execute this query simultaneously that makes you worry? If your three tables indeed have a 1:N relationship, then it is also an option to use triggers to update customercommunications with customerid.

Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Maya Opperman
Sent: 4. november 2008 11:40
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Possible to have an index across 2 tables?

Helen's Query:

select
i.CustomerID,
(select cus.Customer_name from Customers cus
where cus.CustomerID = i.CustomerID) as Customer,
max(c.xDate) Last_Comm_Date
from Incidents i
join Comms.c
on c.IncidentID = i.Incident_id
group by 1,2

----------------
As it looks for my DB:

select
i.CustomerID,
(select cus.companyname from Customers cus
where cus.ID = i.CustomerID) as Customer,
max(c.communicationdate) Last_Comm_Date
from customerincidents i
join customercommunications c
on c.IncidentID = i.id
where (i.customerid = :ICustomerID)
group by 1,2

----------------
My current query I'm using:

select first 1 * /*Note * just here for test
purposes - will be replacing)
from customerincidents ci
left outer join customercommunications cc
on cc.incidentid = ci.id
where (ci.customerID = :ICustomerID)
order by cc.communicationdate desc, cc.id desc

----------------

Using IBExpert's performance analysis tool, both queries are reading
through all comms and all incidents for my selected customer.
I'm using one of my more talkative customers, who have 329 incidents and
2458 comms and it's pretty quick 782 mille-seconds). I'm just worried
that I'm going to cause speed issues sooner or later by reading through
so many records.

If I had the customer id in the comms table, this query could be reduced
to only reading one record.

PS. I'm using FB 1.5. AFAIK using FB 2 won't affect index use at all
(because there's still no index FB2 can use either)