MySQL Fulltext Index not working

MySQL Fulltext Index not working

I recently moved from a standard wildcard search of my news table to making use of a MySQL Fulltext Index. At first everything seemed fine, until I was working with a staging copy of the data with very few rows. The fulltext search would return zero results no matter what string I used.

The query looked like so:

SELECT 		n.id, 
			DATE_FORMAT(n.added,'%b %e, %Y') AS added, 
			n.title, 
			n.user, 
			MATCH(n.article, n.title) AGAINST ('$search') AS score
FROM 		news n
WHERE 		MATCH(n.article, n.title) AGAINST ('$search')
HAVING 		score > 0.25
ORDER BY 	score DESC, n.added DESC, n.id DESC
LIMIT 		$offset, $limit

Researching online, I found that everything appeared to be in order. I of course verified there was a Fulltext Index on the staging copy. I verified the storage engine was myISAM. I verified that the query was working in production.

I found that the default mode was Natural Language which, according to the documentation, has a restriction in the sense that:

words that are present in 50% or more of the rows are considered common and do not match. Full-text searches are natural language searches if no modifier is given.
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

With just a couple rows in the table it is common to hit this 50% limit often. Adding more data to the staging table resolved it. Or you can switch modes to use BOOLEAN.

SELECT 		n.id, 
			DATE_FORMAT(n.added,'%b %e, %Y') AS added, 
			n.title, 
			n.user, 
			MATCH(n.article, n.title) AGAINST ('$search' IN BOOLEAN MODE) AS score
FROM 		news n
WHERE 		MATCH(n.article, n.title) AGAINST ('$search' IN BOOLEAN MODE)
HAVING 		score > 0.25
ORDER BY 	score DESC, n.added DESC, n.id DESC
LIMIT 		$offset, $limit
This is working for me. There are limitations around this mode as well, such as not automatically sorting rows in order of decreasing relevance. That could be a deal-breaker for you. For my stage test, it is sufficient.

Hope this helps.

Mike250

Australian. Sport. Passionate Cricket Fan. Go Pro. Abseiling. Snorkeling. Travel. Golf R. SQL Server Developer. Three sons. One daughter. Last Trip: New York.

Related Posts
Leave a comment

Your email address will not be published. Required fields are marked *