--- In firebird-support@yahoogroups.com, "radevojvodic" wrote:

> Hi all,
>
> Ihave a select statement that looks like this. Can someone help me
> on configuring indexes (and PLAN statement) to boost performance.

Unfortunately, a select statement isn't enough to tell which indexes
to create, it all depends on your data, e.g. an index on mag_id would
be good if it was unique, whereas it would be horrible if 90% of the
records contained the value 11.

But let us look at what you are actually trying to do, maybe we can
improve your SQL.

> SELECT S1.MAG_ID, S1.ROBA_ID, MAX( S2.SDOK_DNROB ),
> S1.CIJENA, SUM( S2.KOR_ULAZ - S2.KOR_IZLAZ ),
> S1.SDOK_DNROB,
> R.roba_naziv
> FROM SDOK S1
> INNER JOIN SDOK S2 ON (S1.MAG_ID = S2.MAG_ID) AND
> (S1.ROBA_ID = S2.ROBA_ID)
> INNER JOIN ROBA R ON S2.ROBA_ID = R.ROBA_ID
> WHERE ( ( (S1.SDOK_DNROB > 0 ) and
> (S2.SDOK_DNROB > 0 ) ) and
> S1.mag_id = 11)
> GROUP BY S1.MAG_ID, S1.ROBA_ID, S1.SDOK_DNROB, S1.CIJENA,
> R.roba_naziv
> HAVING ( (S1.SDOK_DNROB = MAX(S2.SDOK_DNROB)) )
> Order By S1.MAG_ID, S1.ROBA_ID, S1.SDOK_DNROB

Well, I'm confused by your HAVING clause. Is this equivalent to:

SELECT DISTINCT S1.MAG_ID, S1.ROBA_ID, S1.CIJENA,
(select SUM(S2.KOR_ULAZ - S2.KOR_IZLAZ)
from SDOK S2
where S2.MAG_ID = 11 AND S1.ROBA_ID = S2.ROBA_ID,
S1.SDOK_DNROB, R.roba_naziv
FROM SDOK S1
INNER JOIN ROBA R ON S2.ROBA_ID = R.ROBA_ID
WHERE S1.SDOK_DNROB > 0 and S1.mag_id = 11
and not exists(select *
from SDOK S3
where S3.MAG_ID = 11 AND S1.ROBA_ID = S3.ROBA_ID and
S3.SDOK_DNROB > S1.SDOK_DNROB)
Order By S1.MAG_ID, S1.ROBA_ID, S1.SDOK_DNROB

OK, this may well be a different select, so I'll stop guessing now.
Tell us about your data (which fields are selective) and what you are
trying to do with your select (either it is confusing, or it is just
yet another proof that I still have a lot to learn about selects).

HTH,
Set