How do I do a fuzzy match of company names in MYSQL with PHP for auto-complete?

Question

My users will import through cut and paste a large string that will contain company names.

I have an existing and growing MYSQL database of companies names, each with a unique company_id.

I want to be able to parse through the string and assign to each of the user-inputed company names a fuzzy match.

Right now, just doing a straight-up string match, is also slow. ** Will Soundex indexing be faster? How can I give the user some options as they are typing? **

For example, someone writes:

Microsoft       -> Microsoft
Bare Essentials -> Bare Escentuals
Polycom, Inc.   -> Polycom

I have found the following threads that seem similar to this question, but the poster has not approved and I'm not sure if their use-case is applicable:

How to find best fuzzy match for a string in a large string database

Matching inexact company names in Java

Honest

Sorry for mis-editing, I overlooked the second link. – Jessi

My answer below will eliminate the need for a fuzzy search and will provide indexed searching for any partial name - check it out! – Flamsteed

Is a mistery for me how some basic functionality is not built in on an open source project, and even products/companies born because of this (like elastic search). – Motherinlaw

Answer 1

You can start with using SOUNDEX(), this will probably do for what you need (I picture an auto-suggestion box of already-existing alternatives for what the user is typing).

The drawbacks of SOUNDEX() are:

• its inability to differentiate longer strings. Only the first few characters are taken into account, longer strings that diverge at the end generate the same SOUNDEX value
• the fact the the first letter must be the same or you won't find a match easily. SQL Server has DIFFERENCE() function to tell you how much two SOUNDEX values are apart, but I think MySQL has nothing of that kind built in.
• for MySQL, at least according to the docs, SOUNDEX is broken for unicode input

Example:

SELECT SOUNDEX('Microsoft')
SELECT SOUNDEX('Microsift')
SELECT SOUNDEX('Microsift Corporation')
SELECT SOUNDEX('Microsift Subsidary')

/* all of these return 'M262' */

For more advanced needs, I think you need to look at the Levenshtein distance (also called "edit distance") of two strings and work with a threshold. This is the more complex (=slower) solution, but it allows for greater flexibility.

Main drawback is, that you need both strings to calculate the distance between them. With SOUNDEX you can store a pre-calculated SOUNDEX in your table and compare/sort/group/filter on that. With the Levenshtein distance, you might find that the difference between "Microsoft" and "Nzcrosoft" is only 2, but it will take a lot more time to come to that result.

In any case, an example Levenshtein distance function for MySQL can be found at codejanitor.com: Levenshtein Distance as a MySQL Stored Function (Feb. 10th, 2007).

Jessi

Use both; select an initial set of results using soundex, then sort and optionally filter results by Levenshtein distance. – Jacobina

Still the "first letter problem" needs to be taken care of. If you start typing with the wrong letter, the SOUNDEX results will be way off. – Jessi

I don't expect filtering to be needed - I don't expect there will be too many potential matches; rather not enough (or not the right ones). Then it doesn't help to eliminate some of them. – Zenas

Maybe. If the number of options is limited, and a user is presented a mere drop down box, SOUNDEX without further complications will suffice. – Jessi

The link above to the MySQL Levenshtein Distance is broken now. Here's a current link: artfulsoftware.com/infotree/queries.php#552 – Modish

Levenshtein Distance is a fine algorithm. But it's not susceptible to being optimized by any kind of index, like SOUNDEX or (double) Metaphone might be. So if your company database is large your character-by-character match-suggestion scheme might get very expensive. – Pecuniary

With the first-letter issue: you could do the following: when generating the index, delete first letter of the soundex code and store the result in the index. When searching, generate soundex codes for the search term(s) and then strip off the first letter from each of the codes and use the result in the search. – Yanyanaton

Ordering of results should always be done via the score from the MATCH function. – Yanyanaton

Answer 2

SOUNDEX is an OK algorithm for this, but there have been recent advances on this topic. Another algorithm was created called the Metaphone, and it was later revised to a Double Metaphone algorithm. I have personally used the java apache commons implementation of double metaphone and it is customizable and accurate.

They have implementations in lots of other languages on the wikipedia page for it, too. This question has been answered, but should you find any of the identified problems with the SOUNDEX appearing in your application, it's nice to know there are options. Sometimes it can generate the same code for two really different words. Double metaphone was created to help take care of that problem.

Stolen from wikipedia: http://en.wikipedia.org/wiki/Soundex

As a response to deficiencies in the Soundex algorithm, Lawrence Philips developed the Metaphone algorithm for the same purpose. Philips later developed an improvement to Metaphone, which he called Double-Metaphone. Double-Metaphone includes a much larger encoding rule set than its predecessor, handles a subset of non-Latin characters, and returns a primary and a secondary encoding to account for different pronunciations of a single word in English.

At the bottom of the double metaphone page, they have the implementations of it for all kinds of programming languages: http://en.wikipedia.org/wiki/Double-Metaphone

Python & MySQL implementation: https://github.com/AtomBoy/double-metaphone

Lefthanded

The MySQL Double Metaphone implementation is moving to: atomodo.com/code/double-metaphone – Electrophotography

please note that levenshtein is very very heavy on a database, unless you are able to normalize the data, it is not a good option for a medium-heavy used site. – Academic

dm function gives accurate results, as an example please see the output of below two WHER's WHERE dm(first_name) = dm('james') WHERE SOUNDEX(first_name) = SOUNDEX('james') – Reata

Answer 3

Firstly, I would like to add that you should be very careful when using any form of Phonetic/Fuzzy Matching Algorithm, as this kind of logic is exactly that, Fuzzy or to put it more simply; potentially inaccurate. Especially true when used for matching company names.

A good approach is to seek corroboration from other data, such as address information, postal codes, tel numbers, Geo Coordinates etc. This will help confirm the probability of your data being accurately matched.

There are a whole range of issues related to B2B Data Matching too many to be addressed here, I have written more about Company Name Matching in my blog (also an updated article), but in summary the key issues are:

• Looking at the whole string is unhelpful as the most important part of a Company Name is not necessarily at the beginning of the Company Name. i.e. ‘The Proctor and Gamble Company’ or ‘United States Federal Reserve ‘
• Abbreviations are common place in Company Names i.e. HP, GM, GE, P&G, D&B etc..
• Some companies deliberately spell their names incorrectly as part of their branding and to differentiate themselves from other companies.

Matching exact data is easy, but matching non-exact data can be much more time consuming and I would suggest that you should consider how you will be validating the non-exact matches to ensure these are of acceptable quality.

Before we built Match2Lists.com, we used to spend an unhealthy amount of time validating fuzzy matches. In Match2Lists we incorporated a powerful Visualisation tool enabling us to review non-exact matches, this proved to be a real game changer in terms of match validation, reducing our costs and enabling us to deliver results much more quickly.

Best of Luck!!

Dictatorship

Answer 4

Here's a link to the php discussion of the soundex functions in mysql and php. I'd start from there, then expand into your other not-so-well-defined requirements.

Your reference references the Levenshtein methodology for matching. Two problems. 1. It's more appropriate for measuring the difference between two known words, not for searching. 2. It discusses a solution designed more to detect things like proofing errors (using "Levenshtien" for "Levenshtein") rather than spelling errors (where the user doesn't know how to spell, say "Levenshtein" and types in "Levinstein". I usually associate it with looking for a phrase in a book rather than a key value in a database.

EDIT: In response to comment--

1. Can you at least get the users to put the company names into multiple text boxes; 2. or use an unambigous name delimiter (say backslash); 3. leave out articles ("The") and generic abbreviations (or you can filter for these); 4. Squoosh the spaces out and match for that also (so Micro Soft => microsoft, Bare Essentials => bareessentials); 5. Filter out punctuation; 6. Do "OR" searches on words ("bare" OR "essentials") - people will inevitably leave one or the other out sometimes.

Test like mad and use the feedback loop from users.

Zenas

What additional requirements would be helpful? – Honest

+1 for "Levenshtein is designed to detect proofing errors rather than spelling errors" – Verine

Answer 5

Though the question asks about how to do fuzzy searches in MySQL, I'd recommend considering using a separate fuzzy search (aka typo tolerant) engine to accomplish this. Here are some search engines to consider:

• ElasticSearch (Open source, has a ton of features, and so is also complex to operate)
• Algolia (Proprietary, but has great docs and super easy to get up and running)
• Typesense (Open source, provides the same fuzzy search-as-you-type feature as Algolia)

Ceylon

Answer 6

This answer results in indexed lookup of almost any entity using input of 2 or 3 characters or more.

Basically, create a new table with 2 columns, word and key. Run a process on the original table containing the column to be fuzzy searched. This process will extract every individual word from the original column and write these words to the word table along with the original key. During this process, commonly occurring words like 'the','and', etc should be discarded.

We then create several indices on the word table, as follows...

• A normal, lowercase index on word + key
• An index on the 2nd through 5th character + key

• An index on the 3rd through 6th character + key

  Alternately, create a SOUNDEX() index on the word column.

Once this is in place, we take any user input and search using normal word = input or LIKE input%. We never do a LIKE %input as we are always looking for a match on any of the first 3 characters, which are all indexed.

If your original table is massive, you could partition the word table by chunks of the alphabet to ensure the user's input is being narrowed down to candidate rows immediately.

Flamsteed

Answer 7

Check if it's spelled wrong before querying using a trusted and well tested spell checking library on the server side, then do a simple query for the original text AND the first suggested correct spelling (if spell check determined it was misspelled).

You can create custom dictionaries for any spell check library worth using, which you may need to do for matching more obscure company names.

It's way faster to match against two simple strings than it is to do a Levenshtein distance calculation against an entire table. MySQL is not well suited for this.

I tackled a similar problem recently and wasted a lot of time fiddling around with algorithms, so I really wish there had been more people out there cautioning against doing this in MySQL.

Spirometer

Best answer. MySQL is not optimized for string process. PostgreSQL is good choose for this purpose. – Speckle

Answer 8

the best function for fuzzy matching is levenshtein. it's traditionally used by spell checkers, so that might be the way to go. there's a UDF for it available here: http://joshdrew.com/

the downside to using levenshtein is that it won't scale very well. a better idea might be to dump the whole table in to a spell checker custom dictionary file and do the suggestion from your application tier instead of the database tier.

Fibrillation

Answer 9

Probably been suggested before but why not dump the data out to Excel and use the Fuzzy Match Excel plugin. This will give a score from 0 to 1 (1 being 100%).

I did this for business partner (company) data that was held in a database. Download the latest UK Companies House data and score against that.

For ROW data its more complex as we had to do a more manual process.

Tokenism

I just hope this answer is a joke :) – Natch

On a live system is impossible – Doily