>Could someone give me some pointers to improve the speed of this SQL please.
>It's taking
>about 10 minutes to execute on a fast PC using Firebird v1.56.
>
>I am counting the number of messages sent by different methods, for a
>particular company.
>A company has a number of contacts and they receive a number of calls during
>the month.
>These calls can generate a number of messages to be sent out.
>
>This has been working fine for many years, but it appears to execute VERY
>slowly when a
>Company has many contacts ~800
>select m.MSG_TYPE, count (distinct(m.Message_ID)) as MsgCount
>from MESSAGES m
>join MESSAGE_LINK ml
> on ml.message_id = m.message_id
>join CALLS a
> on a.Call_id = ml.call_id
>join CONTACTS c
> on c.CONTACT_ID = a.CONTACT_ID
>where c.company_id=240 AND
> (m.TIMESTAMP_ADDED >= '2015.06.01' AND m.TIMESTAMP_ADDED < '2015.06.30'
> ) AND
> m.TX_Code='O' AND
>group by m.MSG_TYPE
>Here's the plan:
>PLAN SORT (JOIN (C INDEX (I_CONTACTS_A),M INDEX (MESSAGES_TIMESTAMP_ADDED_A),
>ML INDEX (MESSAGE_LINK_MESSAGE_ID_A),A INDEX (PK_CALLS)))
Well, David, I wonder why the optimizer chooses to use indexes for both
company_id and timestamp_added, I'd rather choose the timestamp_added index,
then use the index for the first field in each of your JOINs. If you have an
index for c.CONTACT_ID (hopefully, this is considerably more selective than
C.COMPANY_ID and only a small fraction of their contacts were contacted in
June), then I'd recommend you to try:
select m.MSG_TYPE, count (distinct(m.Message_ID)) as MsgCount
from MESSAGES m
join MESSAGE_LINK ml
on ml.message_id = m.message_id
join CALLS a
on a.Call_id = ml.call_id
join CONTACTS c
on c.CONTACT_ID = a.CONTACT_ID
where c.company_id+0=240 AND
m.TIMESTAMP_ADDED between '2015.06.01' and '2015.06.30' AND
m.TIMESTAMP_ADDED < '2015.06.30' AND
m.TX_Code='O' AND
group by m.MSG_TYPE
Particularly note that I added +0, that is a trick to force the optimizer
choose a different plan, hopefully the correct one. The addition of BETWEEN
probably doesn't matter at all - it's just a long time since I used 1.5 and
don't remember whether it understood that the combination of '>=' and '<' is
similar to BETWEEN or if the index is only used for '>=' and then everything
that's '>=' are compared to '<' without using the index.
Please report back whether or not +0 improves the performance in your case,
Set
