Edwin,

> 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?

GROUP BY cannot be evaluated without sorting, either internal (if indices
are available, aka ORDER plan) or external (otherwise, aka SORT plan).


Dmitry