In the article 45 Ways To Speed Up Firebird Database, it states that
- Use derived tables to optimize
Another way to optimize SQL query with sorting is to use derived tables to avoid unnecessary sort operations. Instead of
SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_N FROM T ORDER BY FIELD2
use the following modification:
SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_N FROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2 JOIN T ON T.FIELD_KEY = T2.FIELD_KEY
T.FIELD_KEY means a primary key column.
In other words, it shunts the sorting to a subquery, and then 'self-joins' to get the rest of the necessary columns.
As I understand it, this will let Firebird do the sorting on a narrower resultset which would use less memory and be more cache-friendly, but surely it shouldn't give that much a boost? Especially when it needs to fetch the other columns afterwards, potentially in a random I/O pattern too.
On the other hand, this reeks of undefined behaviour in that there's no guarantee (in standard SQL) that the engine would retain the order after the join -- it could just join the other way, wouldn't it?
Are my guesses correct? Am I missing something?