I am working on the search feature on my website and would like to improve it a little bit.
My website is a tube website.
I used to work with mysql 'LIKE' statement but realized MATCH() AGAINST() was way more performant.
But I still have a problem with small and partial word.
Let's say there is a title in the video database called: 'Funny video about monkeys - Asia - lol'
There are the different select query with what would be returned (using phpmyadmin to avoid error that would be related by wrong php code)
SELECT video.*, MATCH (title) AGAINST ('lol' IN BOOLEAN MODE) AS relevance FROM video WHERE active = '1' AND MATCH (title) AGAINST ('lol' IN BOOLEAN MODE) ORDER BY relevance DESC LIMIT 18
returns
nothing
SELECT video.*, MATCH (title) AGAINST ('monkey' IN BOOLEAN MODE) AS relevance FROM video WHERE active = '1' AND MATCH (title) AGAINST ('monkey' IN BOOLEAN MODE) ORDER BY relevance DESC LIMIT 18
returns
nothing
SELECT video.*, MATCH (title) AGAINST ('asia' IN BOOLEAN MODE) AS relevance FROM video WHERE active = '1' AND MATCH (title) AGAINST ('asia' IN BOOLEAN MODE) ORDER BY relevance DESC LIMIT 18
returns
the video in the database
SELECT video.*, MATCH (title) AGAINST ('asian monkey' IN BOOLEAN MODE) AS relevance FROM video WHERE active = '1' AND MATCH (title) AGAINST ('asian monkey' IN BOOLEAN MODE) ORDER BY relevance DESC LIMIT 18
returns
nothing
So if I am right, the video is returned only if it contains the exact word, and if the word is at least 4 chars.
But the strange thing is that 'ft min word len' is set to 2 so event the query 'lol' should return the video. Also, the query states 'IN BOOLEAN MODE' so I guess it should return even commons words.
As from the partial word like 'monkey' instead of 'monkeys', I have no idea how to go around this as using 'LIKE %...% OR LIKE %...%' won't be a solution because I need to get the relevance.
Well thank you for reading and helping me.
