Hi,

> I'm looking for a solution, to get better performance with the following
> query
>
> select first 25 RI.PLACE, SR.SWIMTIME, A.LASTNAME
> from RANKINGITEM RI
> join SWIMRESULT SR on SR.SWIMRESULTID = RI.SWIMRESULTID
> join ATHLETE A on A.ATHLETEID = SR.ATHLETEID
> where RI.RANKINGID = 589044
> order by RI.PLACE
>
> when I run this query, I have the following plan:
> PLAN SORT (JOIN (RI INDEX (IX_RANKINGITEM_WORLD),SR INDEX
> (PK_SWIMRESULT),A INDEX (PK_ATHLETE)))
>
> when I look ath the statistics I have 33'000 indexed accesses to all
> three tables.
>
> When I remove the "order by" part, the query is much faster and I have
> 25 indexed accesses to the three tables only.
>
> The plan is:
> PLAN JOIN (RI INDEX (IX_RANKINGITEM_WORLD),SR INDEX (PK_SWIMRESULT),A
> INDEX (PK_ATHLETE))
>
> What I don't understand is, why Firebird server reads all 33'000 records
> in all tables, even if the "order by" depends on RANKINGITEM only and
> there is an index for that on RANKINGITEM.
>
> Anything I can do here with a query plan or something else ?

The Query with the ORDER BY (SORT in PLAN) first fetch all results
internally and perform a sort on the results. After that only 25 records are
returned to the client.
The Query without ORDER BY doesn't need to perform a sort, so it just
returns the first 25 valid records fetched from disk.

If you really need a fast behaviour for this you can create a single index
on RI.PLACE, but probably better is a compound index on RANKINGID, PLACE and
but it in the same order in the ORDER BY clause.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://newsgroups.firebirdsql.info