CODEWITHSUNDEEP
mysqlfull-text-search
Shafizadeh
Shafizadeh

Reputation: 10350

How to eliminate restrictions of full-text for the number of results?

According to my researches, I figured out (in full-text search) if the difference between the total number of rows and number of results be less or equal than the number of results, then result will be zero (or not found anything).

Here is an example, please Take a look at this:

// table - The number of total rows: 3
+----+-------+
| id |  col  |
+----+-------+
| 1  |  one  |
| 2  |  one  |
| 3  |  two  |
+----+-------+

My Query:

SELECT * FROM `table` WHERE MATCH (`col`) AGAINST ('one');

Result: There is 0 result. (Why? I think Because the number of results is 2, also the number of total rows is 3, So 3 - 2 = 1, and 2 > 1, Then the result will be 0.)

Now I want to know, how can I remove this limitation? In other word, I want when I search one, the result be this:

+----+-------+
| 1  |  one  |
| 2  |  one  |
+----+-------+

Upvotes: 0

Views: 32

Answers (1)

HashSu
HashSu

Reputation: 1517

This query show your ft_min_word_len field value and it should be 3 if it is 4 then might not work with length of 3 characters.

mysql> show variables like '%len';

ft_min_word_len | 3 |

change that in my.cnf add below in mysqld block

ft_min_word_len = 3

http://sqlfiddle.com/#!9/71583a/2

try adding IN BOLLEAN MODE in the search pattern as below

SELECT * FROM table WHERE MATCH (col) AGAINST ('one' IN BOOLEAN MODE); SELECT * FROM table WHERE MATCH (col) AGAINST ('two' IN BOOLEAN MODE);

fiddle:

http://sqlfiddle.com/#!9/71583a/2

Upvotes: 1

Related Questions