Re: Efficient sorting the results of a join, without denormalization

Hi Glen:

On Mon, Jun 1, 2015 at 1:16 AM, Glen M. Witherington <glen(at)fea(dot)st> wrote:

> Thanks Francisco, that makes sense. I've started moving my code to that,
> and it eliminates all the performance issues I had.

Happty to hear it. Seems you have a kind of speed-size trade off. If
you can solve it while preserving integrity, great for you.

> I guess I was really hoping there would exist some sort of "dereference"
> option when indexing, so I could dereference a foreign key, and then
> index on a attribute of that row. E.g. So I could have created an index
> such as:
> deref(deref(mail.folder_id).user_id, created_at)

That is difficult, as it would need a way to force mail.folder.user_id
to be a constant or have a lot of rules/triggers ( manual, automatic
or just plain magic ) to update your indexes. On this cases composite
keys let you use composite indexes to accelerate your queries while
preserving normalization, if you can afford them they are nice. The
big problem comes many times if you try to mix them with ORMs and
similar.

Francisco Olarte.