CODEWITHSUNDEEP
mysqlfull-text-search
M1X
M1X

Reputation: 5374

Full Text Search not working with numbers

I have this table with song titles, artist etc.

| id | artist | title | search_tags |

| 1 | miley cyrus | 23 | miley cyrus 23 |

This is my query:

select * from music where match(search_tags) against ('+$search_value*' IN BOOLEAN MODE)

It works fine when I search: miley but it doesn't show any results when search_tags = 23

Note: I'm using MySQL 5.6 with InnoDB on Windows 10 and ft_min_word_len=1

Upvotes: 2

Views: 4383

Answers (2)

barna
barna

Reputation: 171

Notice that ft_min_word_len is 4 by default. The token 500 is length 3. thus it will not be indexed at all. You will have to do three(3) things:

STEP 01 : Open the init file inside mysql folder Add this to \bin\mysql\mysql5.7.24

innodb_ft_min_token_size = 1

STEP 02 : Restart mysql

STEP 03 : Reindex all indexes in the models table You could just drop and add the FULLTEXT index

Upvotes: 0

Jaylen
Jaylen

Reputation: 40371

When searching for "23" will not work because the length of the key is small. MySQL by default stored keys in the fulltext index with a min of 4 characters. you will need to change that to 1 or 2 for your query to work.

What you need to do here is

  1. Add this line to your my.ini file

    innodb_ft_min_token_size = 1

  2. Restart MySQL Service

  3. After the server comes back up, rebuild your tables by issuing a fake ALTER command.

    ALTER TABLE table_name ENGINE=INNODB;

Run the query again and it should work :)

Good Luck

Upvotes: 9

Related Questions