MySQL fulltext search results fine tune

Question

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.

Answer 1

First of all, you can indeed control the minimum word size in your MyISAM fulltext index, with ft_min_word_len.

SET ft_min_word_len= 3

or whatever you need, will do it. You probably want to make sure it's set in your MySQL my.cnf file too, so if your server restarts it's still set.

Second, the word of in your search term is in the FULLTEXT stoplist. You can't use it for matching unless you remove it from the stoplist.

And, if you have managed to include of in your index, notice that the department name strings contain it twice, which will boost their score.

If you change FULLTEXT's configuration be sure to rebuild your index.

Third, as you know the order of your resultset comes from the score assigned to each row by FULLTEXT. FULLTEXT is designed as an assistance to human perception. It presents choices for a human to choose among, rather than precisely correct choices. Expecting perfectly predictable results from FULLTEXT is probably a mistake.

Answer 2

What ever letter We Can Use As Text. If U Have Problem Say Me More Clearly. Ok ?

I Think You Can Use Mysql Query Like This:

select 
    *, count(name) 
from 
    insitutes 
where 
    name like "%London University%" 
order by 
    name desc;

Once Check This If This Statement Works Say Me Else Define Your Problem More Clearly.