Using Fulltext Index Relevance turning up as 0

Question

I have a similar query that I use across several different tables, however on this table, for some reason All of my results are coming in with a Relevance of 0.. Could anyone provide some insite to why this might be happening?

Cheers

SELECT  City,
MATCH (
    Street, City, State, Zipcode
)
AGAINST (
    'San|Diego'
) AS Relevance FROM address_list
WHERE 1 
AND MATCH (
    Street, City, State, Zipcode
)
AGAINST (
    '+San|Diego'
IN BOOLEAN
MODE
)
ORDER BY Relevance DESC 

My full index is on: Street, City, State, Zipcode DB: MyISAM

Works on every other table except this one..

Select * from address_list where City='San Diego' 

produces my list fine, so i'm really stuck.

Ideas?

Answer 1

ft_min_word_len may be the problem. The default is 4, so the word "san" won't be included in the index.

Here's how to check it:

show variables like 'ft_min_word_len';

Answer 2

You should use the same MATCH() in the select clause that you use in the where clause:

SELECT  City,
MATCH (
    Street, City, State, Zipcode
)
AGAINST (
    '+San|Diego'
IN BOOLEAN
MODE
) AS Relevance FROM address_list
WHERE 1 
AND MATCH (
    Street, City, State, Zipcode
)
AGAINST (
    '+San|Diego'
IN BOOLEAN
MODE
)
ORDER BY Relevance DESC