I need to search through around a million records to find out whether an inputted company name is on the database or not, and if it isn't then get suggestions for changing the input depending on what does exist on the database. Presumably FULLTEXT index is the best way to go about this? For example, if the input was 'Some Law Firm LLP' but that is not on the database as a company name, but 'Some Law Firm' is I want 'Some Law Firm' to be returned as a suggestion. Or perhaps 'Some New Law Firm' was on the database I would want that returned.
I have never implemented a FULLTEXT index on a database before so don't really know how to implement one or create the appropriate query to return input change suggestions.
I am also worried about the fact that MySQL does not consider 3 letter strings as words so how would I account for company names like 'BBC'? I know that I can use something like:
$q = "SELECT company_name FROM wfp_contacts2 WHERE MATCH (company_name) AGAINST ('".stripslashes(str_replace(""", "\"", ($query)))."' IN BOOLEAN MODE)";
To test whether there is an exact match on the database, which is fine - but the main point is getting the suggestions. I can't really just use LIKE %'company_name'% because it is far too slow and speed is imperative because there will be up to 700 inputs to be checked for suggestions at once and the LIKE statement with wildcard is far too slow on a million records.
Is there at least any tutorials that anyone can recommend so I can up to speed with FULLTEXT indexing? Also, if there is another way of doing this I would be grateful to hear it as this is extremely important and I honestly don't know what to do about it at the moment.
MySQL Info: MySQL client version: 5.1.41 Storage Engine: MyISAM
Thanks in advance.
