CODEWITHSUNDEEP
mysql
mbouclas
mbouclas

Reputation: 744

mysql fulltext MATCH,AGAINST

I am trying to do a full text search on a field to match specific parts of a string. Consider a long string holding array values like 201:::1###193:::5###193:::6###202:::6. ### seperates an array element and ::: seperates key=>val. Now my understanding of match against is that it can match portions of a string in boolean mode. but when i do something in the lines of 

`SELECT 
a.settings 
, MATCH(a.settings) AGAINST('201:::1') as relevance 
, b.maxrelevance 
, (MATCH(a.settings) AGAINST('201:::1'))/b.maxrelevance*100 as relevanceperc 
FROM 
users_profile a 
, (SELECT MAX(MATCH(settings) AGAINST('201:::1')) as maxrelevance FROM users_profile LIMIT 1) b 
WHERE 
MATCH(a.settings) AGAINST('201:::1') 
ORDER BY 
relevance DESC;`

Table example

CREATE TABLE users_profile (
id int(11) default NULL,
profile text,
views int(11) default NULL,
friends_list text,
settings text,
points int(11) default NULL,
KEY id (id),
FULLTEXT KEY settings (settings)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

i'm getting zero results. Any ideas are welcome.

Upvotes: 0

Views: 889

Answers (1)

RichardTheKiwi
RichardTheKiwi

Reputation: 107776

MySQL fulltext indexes are designed to store natural language words. Your sample

201:::1###193:::5###193:::6###202:::6. ###

Is made up of only numbers as the significant parts, such as 201,1,192... Because very short words are rarely useful, ft_min_word_len is usually set at 4, which means none of the numbers are even in the index.

Fulltext isn't the solution to this problem.

If all you wanted is to count how many times an expression exists in the column, just use

(length(a.setting) - length(replace(a.setting,'201:::1',''))) / length('201:::1')

Upvotes: 1

Related Questions