0

I am having some issues, well not issues, I just found out that this is the expected behavior of MATCH ... AGAINST.

So I have a search engine and it seems that words that are 3 characters or less return an empty row set even though it should be able to pull out the relevant info. Words like php, seo, net, css.

This worked with LIKE syntax, but I switched to full text to improve the search time result and accuracy.

How can I make it work so it can include those type of words as well ? Thank you.

FBK
  • 52
  • 5
  • 20
  • You have to set the minimum word length (there are 2 different variables, depending on if you are using MyISAM or InnoDB), see the link above (or specifically for MyISAM (which you seem to be using): [MySQL full text search for words with three or less letters](https://stackoverflow.com/q/1585611)) – Solarflare Aug 09 '19 at 07:57
  • I added the ft_min_word_len = 1 for MyISAM and restarted the service, also i repaired the table as suggested on the mysql website after each change... but it is still not taking it, still returning 0 results. – FBK Aug 09 '19 at 09:26
  • Then you are either: a) not using MyISAM (check `show create table tablename`, at the end there is `engine=...`) b) Not properly setting the configuration (check `select @@ft_min_word_len;`) c) Not properly recreating the index (`repair table tablename quick` should work for MyISAM, I assume you did that; maybe also try to drop+recreate the fulltext index just to be sure) d) The problem is unrelated to the wordlength, but to the count. If the term appears in 50% of all rows, they are not returned. To check for this, try the boolean mode (`match (...) against ('php' in boolean mode)`). – Solarflare Aug 09 '19 at 09:46
  • I think the problem is that it did not took the modification i made, even if i did the repair stuff... as when i did the select @@ft_min_word_len; it shows still 4. – FBK Aug 09 '19 at 09:48
  • My /etc/my.cnf looks like so:[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd ft_min_word_len = 2 innodb_ft_min_token_size = 2 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid – FBK Aug 09 '19 at 09:49
  • The fix was to restart the mysql service like so and it worked: sudo service mysqld restart --console --ft_min_word_len=2 – FBK Aug 09 '19 at 11:21
  • This is only a temporary fix, the next time you restart your server that setting will reset. The most common reasons why your config files do not work are: a) you didn't restart b) you have several config files and the setting is redefined in another one. Check the locations mentioned [here](https://mariadb.com/kb/en/library/configuring-mariadb-with-option-files/#options) for additional files; maybe try `--print-defaults` c) misspelling (although it looks ok); maybe remove the spaces around the `=` (although it is allowed to use them) – Solarflare Aug 09 '19 at 11:39

0 Answers0