[slavveras]

Couldn't you also use a materialised view here? Or, alternatively, what about an index on the documents table alone? It might depend on whether your db has a flexible enough indexing system to do what you want, but I don't see why the index would have to span tables since it only needs to depend on the folder id and the document modification time

[tlarkworthy]

A materialized view is another approach but thats still essentially denormalization. I prefer using indeces as they are a bit more of a 1st class relational concept in my mind. With a MV you are copying ALL table data, but with an index you can concentrate on just the ordering columns (you can make a few column MV too and then join but you are now reinventing indexes).

You cannot get an index to be used across a join in most relational DB system. But joins pop up everywhere when you normalize.

This query is a fairly distilled example of it https://stackoverflow.com/questions/16402225/index-spanning-...