[hawkice]

If you do N self-joins in a row, creating the index on the fly would be a good move, because they'd have to read through the same data multiple times. The real world example that springs to mind is comments that can nest up to e.g. 5 layers, where it kinda makes sense to grab it all in one query like that.

Obvious caveats: I would just have a 'top-level-post-id' so I could grab everything I need and use the foreign keys to arrange them in memory, not for discovering the rows I need, also wowzers doing _any_ joins without an index is silly, forget multiple in one query.