I would like to program a new view that might possibly replace the Friends view in the future, which will do the same thing as the Friends view except that it sorts the postings by the date of the most recent comment.
This was suggested here and probably numerous other times.
Before you say "No way, that's too much of a DB hit", please have a look at my suggestions for implementation:
I would like to create a new database table. Lacking ideas for a better name, I'll refer to it as 'friendssort'. This table will have three columns: userid, postingid, position (all integers, and all indexed). The new Friends view would simply select everything from this table for $remote's userid and get all the postingids for the page, and their order by sorting by position. Ideally, position should be continuous without holes for each userid, so that one single SELECT statement can be used to extract the 20 top entries. Something makes me think that this actually reduces the load on the database compared to the current Friends page generator. Correct me if I'm wrong.
Now, this table would need to be updated every time someone posts a new entry or leaves a comment. For everybody on the Friend-Of list of the owner of the journal posted to, the relevant postingid would either have to be added to the list, or moved to the top. Granted, this is an additional hit on the DB, but not a big one, and it might cancel out with the load reduced above. People reload friends pages more often than they post comments or actual entries :)
Anyway we never actually need to check the dates on a potential lot of comments.
There is only one major drawback I can think of, and that's the problem of what should happen when you add a friend. Either we actually do have to look at the date of lots of comments to put at least some of the user's most recent postings into the friendssort table, or we simply do nothing, and the friend's postings will not appear on this page until they get commented on.
Then there's another question which I couldn't solve. Should the position column be in ascending order or descending order? In other words, would position = 1 mean it's the first (top) posting on the page, or the very last (bottom after hundreds of skips). If 1 is the first posting, then inserting a new posting would mean to increase the number on every posting below it (which might be a lot of postings). If 1 is the last posting, we would need to find out MAX(position) before we can extract the 20 most recent.
Please comment here and pour out all your thoughts about this. Please let me know how feasible this is, and whether there is still too much of a DB hit, maybe you have ideas how to resolve those, etc.etc. Any input. Anything.