I've a database with around 1.9 Million rows. My DB details
Server: Localhost via UNIX socket Server type: Percona Server Server version: 5.5.42-37.1 - Percona Server (GPL), Release 37.1, Revision 727 Protocol version: 10 User: ****@localhost Server charset: UTF-8 Unicode (utf8)
Currenty Using:
I'm using the following algorithm on my FULLINDEX column First, I match my required string as per the below query
SELECT title FROM my_db WHERE MATCH (`Title`) AGAINST ('my string' IN BOOLEAN MODE)
And then I use a levenstein()
distance function on the results row server side through PHP to get the closest match to the string.
My Questions:
- Would it be faster to implement a levenshtein entirely instead of a MATCH, AGAINST on a fulltext for such a huge database?
- Is there any implementable algorithm which is better than levenshtein or for that matter the best such algorithm existing right now?
- Is there any other work around instead of
MATCH
orlevenshtein()
? - How will each the below search modifiers
IN NATURAL LANGUAGE MODE, IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION, IN BOOLEAN MODE, WITH QUERY EXPANSION,
enhance or optimize my searching and results? Hence, which one would be the best?
Thanks!