I have a MySQL InnoDB table with a "name" column (VARCHAR (255)) with which I want users to be able to search by returning all the matching rows. However, I cannot just use the LIKE query because the search should allow users to enter names that are similar to the available names (for example, prefix with "The" or not knowing that the correct name includes an apostrophe).
Two examples:
Name in the database: "Rose and crown"
Examples of possible searches that must match: "Rose and crown", "Rose and crown", "Rose and crown", "Rose and crown"
Database Name: 'Diver Inn'
Examples of possible searches that must match: 'Divers' Inn', 'The Diver Inn', 'Divers Inn'
I also want to be able to rank the results according to the relevance of the “closest match”, although I'm not sure how this will be done (maybe edit the distance?).
It is unlikely that a table will ever grow over several thousand rows, so a method that does not scale to millions of rows is good. After entering, the name value for this row will not change, therefore, if an expensive indexing operation is required, this is not a problem.
Is there an existing tool that will perform this task? I looked at Zend_Search_Lucence, but it seems to focus on the documents, whereas I'm only interested in finding a single column.
: SOUNDEX , . :
SELECT soundex( 'the rose & crown' ) AS soundex1, soundex( 'rose and crown' ) AS soundex2;
soundex1 soundex2
T6265 R253265
: Zend_Search_Lucence , , , , , . , , 3-4 .