CODEWITHSUNDEEP
mysqlfull-text-search
user101289
user101289

Reputation: 10422

mysql match / against syntax

I have a varchar column in my db that I'd like to search for words using a mysql MATCH / AGAINST query. However, if there's multiple words it doesn't appear to be working.

INSERT INTO mytable (title) VALUES ('my id 123');

SELECT * FROM mytable WHERE MATCH (title) AGAINST ('+my +id +123' IN BOOLEAN MODE);

Returns no results. Am I missing something in the query syntax? Note that the title field does not have a full-text index.

Upvotes: 1

Views: 1193

Answers (2)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

You need to setup the following:

STEP 01: Create a stopword list

echo "a"    > /var/lib/mysql/stopwords.txt
echo "an"  >> /var/lib/mysql/stopwords.txt
echo "the" >> /var/lib/mysql/stopwords.txt
chown mysql:mysql /var/lib/mysql/stopwords.txt

This will prevent indexing the most comment word articles

STEP 02 : Add this to /etc/my.cnf

[mysqld]
ft_min_word_length=1
ft_stopword_file=/var/lib/mysql/stopwords.txt

STEP 03 : Restart mysql

service mysql restart

STEP 04 : Create the FULLTEXT index

ALTER TABLE mytable ADD FULLTEXT ft_title (title);

Afterwards, your query should work, or at least produce tangible results.

Give it a Try !!!

Upvotes: 1

Wrikken
Wrikken

Reputation: 70520

my, id & 123 are probably lower then ft_min_word_length. I hesitated whether numerics would be considered words, a quick test reveals they are, but the minimum (string)length still applies.

Upvotes: 0

Related Questions