I'm working on a site which has many features similar to LJ (but is not positioned as a competitor to it), including posts from users, friends lists, and varying security levels for viewing posts (anyone, site members, friends, etc). These features are all working fine in my prototype, but the way I'm going about it is definitely going to be a bottleneck in the future.
Currently, in the method to get the latest post for a user, I get all of the posts in reverse order, then run each through the constructor for my Post class, which has a "viewer" argument which is an instance of the User class representing the logged in user. The constructor takes a query to get itself together, another to see if the viewer can view the post, and either returns the Post object or nothing at all.
So, if someone makes a thousand posts, but all are friends-only except for the first, I'm running 999 through this constructor and its queries before finding the one public one. Obviously this is stupid, but I'm unsure of a better way to go.
One of the fields in the Posts table is "security", which is an int representing one of a bunch of security levels. I'm really not savvy enough to think of a query that lets me do "if it's security level 3, join in the user-relationship table and check this, but if it's 5, don't bother because it's private."
Or maybe that's the wrong line of thinking entirely, I'm not sure. Any DB/architecture people have any general thoughts? Any LJ people want to point me at some relevant things in CVS? I browsed around a bit but I'm not familiar with where things are.
(btw, this is OO-PHP and MySQL 4.0.17)