FirebirdSQL/問答篇/Performance with joins and order by

出自VFP Wiki

在2006年2月1日 (三) 13:21的最新修訂版本

如何改善 joins and order by 的執行效率

• 問題描述

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 ?

cu Christian

• 解決方式

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