Want to replace fulltext, and %foo% search with something better (and faster).

Question

I have a site (built in php) where I have roughly a million records in a mysql table. There is a very complex "advanced" search which allows the data to be searched, sorted, and ordered in hundreds if not thousands of various ways.

Unfortunately, mysql search isn't that good, and is extremely slow. Average search takes 5 seconds currently, and the only way I can make the site function is by caching all the searches for a week (there are over 1.1 million cache files just for searches currently). I have "ghetto fuzzy search" which I implement via the soundex() function.

I wanted to see what I can do about replacing the mysql based search with something a bit faster, and something that would return accurate results. I also need the output to be totally skinnable, as the results page isn't just text, but pictures, and complex css.

I looked at sphinx, but there is no fuzzy search there, which I'd very much like to have.

Answer 1

Are you indexing your tables?

Answer 2

SQL isn't designed for the kinds of queries you are performing, certainly not at scale. My recommendation would be as follows:

Index all your tables into a Apache Solr Index. Solr is a FOSS search server built using Lucene and can easily query index in milliseconds or less. It supports pretty much any type of query you need: fuzzy, wildcard, proximity etc.

Since Solr offers a RESTful (HTTP) API should easily integrate into whatever platform you are on.