January 2nd, 2013, 10:50 PM
Best Practices for Full Text Searching
I'm currently redeveloping a site, or more accurately it's underlying web service, that is essentially a search engine. Right now it employs MySQL fulltext searching on a static dataset that's only around 40,000 records of short text. No heavy lifting.
I'm about to do a complete re-write to form a more solid codebase and expand the API so I've decided to revisit the DB as well.
With other choices such as PostgreSQL and SQLite offering fulltext searching, should I be considering a DB migration as well? Then there's Solr and others as well to be considered. My main concerns are performance and expandability. Usage studies show that there are very few repeated searches so features like caching even tend to fall pretty low on the list of considerations.
I'm considering a switch from Apache to Lighttpd on the web side, so I'm really not opposed to any ideas on the DB side as I'm likely going to be configuring new servers from the start.