First, consider the log table. It's almost 3 gig now, and it's structured like crap, I now realize.
The index is on (ownerid, posterid). Ownerid should really be called "journalid" to be more consistent. Owner was used before community journals when I started using journalid more.
The problem with that index is that it only lets you quickly get at entries when you know either the ownerid, or both the ownerid and posterid (prefix matching on indexes, ya know?)
So, if I want to ask LJ to show me all of my journal entries I've posted anywhere, it can't. Pathetic.
Also... I can't post an entry to multiple communities at once (ala newsgroups). Pathetic
See what I'm getting at here?
There's an ALTER TABLE coming up for the 2.7 gig log table. Oh boy.
It's going to be split into two tables... one holding items, and one keeping track of what items are in what journals.
I'll probably have to write some script to do the import slowly over a long period of time ... it's going to be a pain. But while we're at it, we can delete old journals, and we can start compressing old, long journal entries. Dormando is going to write me a UDF function for MySQL that wraps zlib.
So, we do that and then we can cross-post to journals, and we can quickly find ever entry you've posted.
Then, I want to be able to give journal entries unique character names, to be exported into the URL namespace. Think: http://www.livejournal.com/~bradfitz/new_years_eve_2001
Then, I want to be able to list relationships between nodes (er, journal entires). So I can have a "class schedules" node that has children for each quarter I was in college, and my schedule then.
See--- journal entries no longer have to be attached to a journal. :-)
Basically, I want to give every LJ user and community their own everything2 site, journal, and collaboration tools.
Then, I want to build a kick-ass website to replace my old and stale bradfitz.com, using only livejournal.