I'm working on a project that involves records with fairly large numbers of fields (~15-20) and I'm trying to figure out a good way to implement deduplication. Essentially the records are people along with some additional data. For example, the records are likely to include personal information like first name, last name, postal address, email address, etc. but not all records have the same amount of data.
Currently records are stored in a RDBMS (MySQL) and I want to detect duplicates on insertion and still have them inserted but flagged as a duplicate. It needs to be fast as I need to provide feedback as to if it is a duplicate or not in real time. The dataset is large (millions of records).
I've considered the following options but I'm not sure which is best/if they are better options available:
- Use MySQL's built in fulltext search and use fuzzy searching. Major issue with this is that is seems slow, only the latest version supports fulltext indexes with InnoDB (alternative engine is MyISAM which is not good and critically does not support transactions) and fuzzy searching alone does not seem the best method for similarity detection.
- Use simhash or similar. Issue with this is that I'd also like to be able to detect synonyms which I don't see how simhash handles this. For example, address might be: "Some Road" or "Some Rd." and names might be: "Mike" or "Michael"
- Index the data using an Apache Lucene derivative (elasticsearch/solr/etc) and perform a query that would likely return numerous results.
In terms of using Apache Lucene I've been reading about similarity detection and using cosine similarity to produce a value from 0 to 1 from the term frequency vectors that lucene stores. I could apply this to the results from the lucene query and check to see if any of the results are above a certain threshold. My concern about this is how relevant the cosine similarity would be for the type of data I'm storing, i.e a number of fields with either single or a small number of words compared to calculating the cosine similarity of a comparison of some large text document.
Basically, I'm wondering what is the best way to deduplicate this type of data (or put alternatively, detect similarities with this type of data)?