CODEWITHSUNDEEP
mysqlfulltext-index
Khanh Tran
Khanh Tran

Reputation: 1846

Mysql fulltext search on character based

In our database, we have a table topics with title column in it. This column often contains values with 2 or 3 words such as: 'pet', 'love', 'film', 'pop music'...

Now, I have to perform a search against this field which should return the best matches for a given characters.

I have tried BOOLEAN MODE and WITH QUERY EXPANSION but none of those returns any thing unless the term I provide matches at least one word with the values in the column.

Does Mysql provide the capability to search on characters bases?

Upvotes: 1

Views: 209

Answers (1)

Yogesh Salvi
Yogesh Salvi

Reputation: 1212

MySQL full text search has various configuration settings, which can be changed to resolve this issue:

1) ft_min_word_len : This indicate minimum word length mysql should consider while preparing full text index. Its default value is 3. So by default mysql can not search any word whose length is less is 3. In case you have any such case, you can modify this flag in /etc/my.cnf (linux environment).

2) Stop World List: MySQL has in build stop world list for full test search [http://dev.mysql.com/doc/refman/5.0/en/fulltext-stopwords.html]. MySQL full text search never include any of the stop world in full text index. You need to overwrite it by setting stop world list to new list in /etc/my.cnf (linux environment)

ft_stopword_file="/new_stop_word.txt" Please make sure, mysql user has access to this file.

Once you made changes, restart mysql service and quick repair the table which has full text index.

Upvotes: 4

Related Questions