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.

closed as off-topic by user53019, Dynamic, Robert Harvey, World Engineer Dec 26 '13 at 3:13

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions asking us to recommend a tool, library or favorite off-site resource are off-topic for Programmers as they tend to attract opinionated answers and spam. Instead, describe the problem and what has been done so far to solve it." – Community, Dynamic, Robert Harvey, World Engineer
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 3
    Your question requires too many details of your situation in order to answer it properly. You would be better off setting up prototyping environments and investigating the various tools you mentioned. That's the only way you'll find which one is easiest for you to use and which one meets your needs. – user53019 Dec 19 '13 at 14:52
  • 1
    I guess my main question is, is it suitable to use solr to query a list of names using fuzzy matching? – NibblyPig Dec 19 '13 at 14:55
  • 3
    Have you looked at solr? Have you read their website? I would think that sort of question (and more) would be handled through their documentation. If you can't figure it out from their documentation, then maybe that's a sign not to use their product. – user53019 Dec 19 '13 at 14:57
  • 3
    SLC - No worries about being direct, and allow me to reciprocate the courtesy. The first problem is that your question as written is off-topic for Programmers. It's either too broad, an off-site resource recommendation, or primarily opinion based. Even if we focused on the fuzzy-logic matching, it's still not a good fit for the site as it remains an off-site resource recommendation. I don't see a programming whiteboard type question in your current question. – user53019 Dec 19 '13 at 15:14
  • 3
    @SLC To be curt, we aren't your paid consulting service, or even paid :) If you want enterprise level answers for your specific case you should seek enterprise level consulting – Ampt Dec 19 '13 at 15:18

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.

  • Thank you for your response that's very good to know. I'm trying to work out on balance if we should invest time in pursuing this or if there is an alternative that is better designed for the specific task of intensive matching of short words. This post really helps, thanks. – NibblyPig Dec 19 '13 at 17:51

Not the answer you're looking for? Browse other questions tagged or ask your own question.