The best way is to avoid using a database at all and instead use search engine technology, as seen in Google and Yahoo. The best open source contender is Lucene, a java application that is widely ported and integrated with other languages.
At the moment PHP seems to be lagging behind other languages in their support for this technology, but the recent emergence of 2 seperate Web Service-enabled Lucene search engines may be the key to breaking through this barrier (though sadly this probably rules it out for most people running Moodle on standard commercial hosting, or at least increases the complexity greatly).
The second is more recent and more advanced.
The most readable introduction to the relevant technology is the On Search series written by Tim Bray.
However, given the lack of PHP-Lucene integration, and the portablity needs of Moodle it seems likely that lowest common denominator SQL methods will be the best available technology for the moment.