SELECT entry FROM book WHERE entry LIKE '%foot%' AND rand = 5001;
Doing a search like that where rand is the indexed field, against 25k records returns 33 entries in 1.5 seconds. Now most LiveJournal users do not have 25k records so it would be faster than that. Even Brad only has 6396 entries. Looking at the schema of the DB here, it looks like LJ should be just about the same as my test. Plus LJ has much bigger hardware than what I used (Dual P2-450, Raid5).
Is there a reason not too allow full-text searching against a single users journal or even their comments? I would think if it was set it up to only query off some slave DB server it wouldn't make much of a performance difference. Maybe make it a paid user only service.
It would be really handy to be able to reference all past entries where I used the word 'jigga' if my current entry was about jiggas.