> This presents a problem because the most recent entries will naturally be at the very end of the database, and it seems that searching with large offsets (or reverse order) takes more time.
No. MySQL can (usually) search backwards just as fast as forward. Offsets, per se, are not an issue.
FULLTEXT is much faster than LIKE '%word%'.
Check out Lucene and Sphinx a alternatives to FULLTEXT.
For your query:
SELECT *
FROM table
WHERE title LIKE '%term%'
OR content LIKE '%term%'
OR categories LIKE '%term%'
OR tags LIKE '%term%'
ORDER BY date DESC
LIMIT 0 , 30
converted to FULLTEXT:
SELECT *
FROM table
WHERE MATCH ('+term') AGAINST (title, content, categories, tags IN BOOLEAN MODE)
ORDER BY date DESC
LIMIT 0 , 30
You wild need a single FULLTEXT index with all the text fields:
FULLTEXT (title, content, categories, tags)
Vertical partitioning (splitting off a second table) would not benefit you much -- in this case.
> "blue widgets"
WHERE MATCH ('+blue +widgets') AGAINST (title, content, categories, tags IN BOOLEAN MODE)
> proximity search
Hot off the press!
http://forums.mysql.com/read.php?107,428270,428270
If you chase your design, you will get quagmired in designing, implementing, improving, debugging, etc, it. Just use FULLTEXT, and live with its limitations. "Time to market" is important for most of us.