Svein Erling Tysvær wrote:

> Seeing the query, view and index definitions (so that we know which
> fields are involved) and the plan normally helps quite a bit. Also, it
> would be good to know which version of Firebird you're using and how
> selective the indexes in the fields are. And how many rows does your
> query return?

I put this up on stackoverflow, I hope it's OK if I link over there.
That shows the schema example.

http://stackoverflow.com/questions/1666962/speeding-up-sql-query-when-sorting-on-foreign-keys

The PLAN for that SELECT query looks like this:

PLAN SORT (JOIN (USERS INDEX (RDB$PRIMARY1), DATA INDEX
(DATA_USER_ID_IDX, RDB$PRIMARY3), DATA_SET INDEX (RDB$PRIMARY2)))

The main big table "data" has about 1.2 billion rows and the "data_set"
table has about 800 million rows. I was wrong about the result set size
in my stackoverflow post, the result set varies but is usually around 10
million rows (it can be more but not on average).

If I run the query without ORDER BY then it starts returning results
quickly. When I add the ORDER BY Firebird starts filling the temporary
filesystem with a huge amount of data and it takes forever to run.

Thanks