I have an InnoDB table with 5000 rows. Here is an example of my table named 'insitutes'.
id| name
1 | University of London
2 | Department of Maths University of London
3 | Department of Biology University of London
4 | Department of Chemistry University of London
5 | Department of Physics University of London
...
This is what my query looks like
SELECT *,
MATCH (name) AGAINST ('London University' IN BOOLEAN MODE) AS score
FROM insitutes
WHERE MATCH (name) AGAINST ('London University' IN BOOLEAN MODE)
ORDER BY score DESC
This is what my result will look like
Department of Biology University of London
Department of Maths University of London
Department of Chemistry University of London
University of London
....
I want to get 'University of London' as the first result. Saying this I mean I want to get the closest match to the search query.
By playing with my data I found out that changing the table type to MyISAM and modifying the query to 'IN NATURAL LANGUAGE MODE' will give me expected results. But I cannot use the table type MyISAM as it does not indexes words less than 4 charaters.