FirebirdSQL/問答篇/Performance with joins and order by
出自VFP Wiki
如何改善 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
