problem with SQL sintax for MATCH AGAINST in pset8

Question

I created the fulltext index for the three colums:

This is my query:

FROM places WHERE MATCH ( postal_code, place_name, admin_name1 ) AGAINST ( '+New +Haven' IN BOOLEAN MODE )

and I get the following result:

shouldnt' I get only the results with both words New and Haven? What am I doing wrong...?

Thanks in advance!

Answer 1

"new" is a mysql stopword. Which I have interpreted to mean "noise" word, and thus ignored. It is also length 3; mysql minimum fulltext word length default is 4. If you try a MATCH against '+New' only, it will return 0 rows.

Here is one way to correct for both problems:

1. Create a file in your home directory called user_sett.cnf with contents:

[mysqld]
ft_min_word_len=2
ft_stopword_file=""

(NB. "" is doublequote-doublequote)

Setting ft_min_word_len to 2 will enable search by state abbreviation. The other essentially turns off stopword checking. Since our dataset is very small and specific that should not affect performance. You can search around the webs to find other solutions.

2. Copy the file to /etc/mysql/conf.d, you will need elevated permissions:
   sudo cp user_sett.cnf /etc/mysql/conf.d

3. Restart mysql50 (mysql50 stop; mysql50 start). Relaunch phpMyAdmin.

4. Rebuild the fulltext index in phpMyAdmin. You can use the "repair table" option under the operations tab (at the bottom of the page, under table maintenance). Or you can remove and readd the index. To remove, there is a tiny +Indexes at the bottom of the structure tab.