user783402 user783402 - 9 days ago 5
MySQL Question

MySQL search text column for a specific word

What I would like to do is search for a specificity word in my table like so:

SELECT * FROM my_table WHERE MATCH (description) AGAINST ('test');


And this will return all the rows in my_table that contains the string 'test'.

The problem is this dose not always happen. Meaning it will work sometimes after I have recreated the table.

CREATE TABLE my_table
(date date,
time time,
name varchar(50),
description text,
FULLTEXT INDEX (description)) ENGINE=MYISAM;


Because it only happened sometimes it has led me to believe that there must be some problem with the indexing.

So I tried setting the ft_min_word_len to 1 that did nothing.
My MySQLVersion is 5.1.57-community.

Thank you for your help.

pif pif
Answer

match and against is a natural language search. if your column contains 50% or more of what you pass in the against() parameter, it will be considered common and would not return anything. just use SELECT * FROM my_table WHERE DESCRIPTION LIKE '%test%' where the % wildcard depicts that there is a word or phrase or suffix/prefix before or after.

here read http://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html to learn more