CODEWITHSUNDEEP
mysqlindexingfull-text-search
user783402
user783402

Reputation: 13

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.

Upvotes: 1

Views: 8193

Answers (1)

pif
pif

Reputation: 96

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

Upvotes: 2

Related Questions