More advanced searching of MySQL records

Question

I have a personnel database of 10,000 individuals. I am developing a PHP CMS to manage and search these individuals.

One of the complaints about the old system was that you had to search for names by their exact spelling, else no results would return.

The old system was using MySQL LIKE statements, which are too exact (and I do not want to burden my users with explanations on how to use wildcards)

I have heard of systems such as Sphinx and Solr - these are impressive and very robust, but ideally I would like to avoid having to install additional software on the server sand conduct a lot of configuration (or if I do, the simpler the better)

What kind of system would you suggest that provides "smarter" keyword interpretation (primarily matching closely spelled words)?

Answer 1

You can take a look at whether mySQL's SOUNDEX() / SOUNDS LIKE does the job for you. If it doesn't, you may indeed need an external search engine like one of the ones you mention.

But first be sure to have taken a look at mySQL's full text search functions. They are not very flexible in regard to the keywords entered, but they offer a lot of additional features like boolean searches +Miller -Peter out of the box. Maybe that can help your customer's situation already.

Answer 2

Solr really is a great option for you. Not only does it provide soundex support, it has language stemming and a ton of other features which you can leverage down the road. Also, being separately hosted allows you to scale your search traffic separately from your database traffic.

If system administration isn't your thing, you might consider a hosted service like Websolr (full disclosure: I am one of its co-founders), which was built precisely to deliver all the killer search features of Solr to developers who don't have the expertise or inclination to configure, monitor and performance-tune Solr itself.