Hi Edwin!

At 17:01 17.07.2003 +0700, you wrote:

>hi,
>
>this query takes several seconds to complete:
> SELECT t.app AS app, COUNT(1) AS total, SUM(t.price) AS amount
> FROM dlr_localtime dlr JOIN mt_localtime mt ON dlr.id = mt.id
> JOIN tariff t ON t.id = mt.tariff
> WHERE mt.tariff IS NOT NULL
> AND dlr.status = 'delivery success'
> AND dlr.mtype = 'MT'
> AND mt.ts >= '2003-07-17 00:00:00' AND mt.ts <= '2003-07-17 16:31:59'
> AND mt.smsc = 'foo'
> GROUP BY mt.tariff, t.app
>
>dlr.status has 5 possible values, dlr.mtype has 2 possible values.
>indexes are created for mt.ts, mt.smsc, dlr.id, mt.id, t.id
>
>The query plan is:
>PLAN SORT (JOIN (DLR DLR INDEX (DLR_STATUS_IDX),MT MT INDEX
>(RDB$PRIMARY29,MT_SMSC_IDX),T INDEX (RDB$PRIMARY24)))
>
>Why is it doing sort? How to make the query run faster?
>
>rgds,
>Edwin.

What a great example to show what I just answered! Your problem is that MT
is using two indexes - even though one of them is unique! Change your select to

SELECT t.app AS app, COUNT(1) AS total, SUM(t.price) AS amount
FROM dlr_localtime dlr JOIN mt_localtime mt ON dlr.id = mt.id
JOIN tariff t ON t.id = mt.tariff
WHERE mt.tariff IS NOT NULL
AND dlr.status = 'delivery success'
AND dlr.mtype = 'MT'
AND mt.ts >= '2003-07-17 00:00:00' AND mt.ts <= '2003-07-17 16:31:59'
AND (mt.smsc = 'foo' or 2=0)
GROUP BY mt.tariff, t.app

and it should run faster (provided that smsc is the field indexed in
MT_SMSC_IDX and that you are using Firebird 1.0)! Please tell us how it
works out.

Set