To simplify the recursive function (using data in memory, not in the database), you could also have another field, called indent, that stores the level in the comments.

You could find all the comments with one SQL statement (where newsid = news.id), then go through each level of each comment, and sort it that way.

You could even make the parentid field a double, so you would store 123.4 where 123 would be the parent and 4 would be the indentation level.

I guess the difference is, do you want to store everything in one field or have different fields for everything?

I think this gives the original poster many options to think about. To me, a recursive function would be easier to write than coming up with ways to combine multiple fields into one using MySQL.

I think the order of optimization is: 1) Optimize so you can use a cached result if it exists 2) If the cache is out of the date, try to make just one SQL call 3) don't use table joins 4) reorder the data as needed in memory, then save it to a cache

As far as the single SQL call goes, I don't think it matters whether you have 5 fields or 30 in the actual table as long as you only request the fields you need, but I could be wrong.

By the way, do you have any other interesting examples?