At the moment, we’re using sql server full text search, but it’s too inflexible.
The main thing we do is look up names of people from a database based on a search query. The searches need to be fast, and they need to be fuzzy. SQL Full Text Search doesn’t really support fuzzy matching especially when combined with the thesaurus option. Therefore I need a better solution.
My research suggests that lucene and solr are widely used enterprise solutions, but my searching suggests these are more designed for indexing things like documents and webpages, or what it refers to as ‘unstructured data’.
Our data is very well structured, and therefore I’m unsure if it’s suitable for this type of work or if I should be investigating another product. According to the book I have Solr 1.4 Enterprise Search Server it supports all of the above except for prefix matching out of the box, however it states there are performance issues with substring searches.
Do you think that solr/lucene is a good technology to investigate for solving my problem? If not, do you have an alternative?
Any advice is welcome. I am a .NET developer, hence solrnet rather than solr.
11
I have only experience with Solr and Sphinx, so can’t really compare too much. And we don’t use much ‘fuzzy’ search. But I worked with Solr a lot and think I know the docs quite well.
First of all, the term ‘document’ is to be understood in a very technical way. By no means does this limit your search to typical text documents. We use Solr to search products from our database and in this context a document is just a denormalized data stream representing a single product with all the text information normally in related tables directly attached to the product document. So whatever you want to search for, if it has some kind of unique id and textual descriptions (tags, category, assortment, brand…) it qualifies as document.
Ok, back to fuzzy. This kind of search is difficult, because you can’t really make much use of indexes here. You would have to compare a string against every single indexed string and calculate some kind of ‘distance’ value and then select against a maximum distance. Solr offers Fuzzy Search and Proximity Search, but since we don’t use it, I can’t say how good they perform. But as one can read on the internet from version 4.0 Lucene uses something called Levenshtein Automata which should enable fuzzy search on very large indexes.
Maybe interesting here too us the way Solr builds the index. Before indexing every string goes through filters and tokenizers. There some of the magic happens and you can strongly influence how good the index will be for your kind of data. There are already a lot of default Filters and Tokenizers, but you can even write your own. So maybe there would be ways to improve performance here a bit.
Beyond that there are a few things in which Solr is really great. Mainly facetted search, where you search and count for example how many products are in a given assortment. Just make a list of all assortments and counts, then go on and make the same list in near real time together with a search term. Or select another facet (say brand=CocaCola), a search term (q=light) and again get a list with all assortments and number of products. This cross referencing came out so nice and fast that we actually replaced nearly every single SQL product listing or searching on our web site with Solr queries.
1