MySQL – fine-tuning full-text search

full-text-searchMySQL

How can I fine tune MySQL full-text search for my information domain?

For example, I might have a keyword like js that I would like the search to recognize, but I don't think allow a minimum word length of 2 be a good idea.

Another scenario I have: I'd like to ignore 3 if that was searched, but would like it to be recognized if the search was css 3. Not all single-characters are insignificant, particularly when combined with another string.

Is there a way I can give MySQL a list of words I do not want it to exclude either through it's stop words list or minimum word length variable?

Best Answer

Assuming your table is ENGINE=InnoDB, here are some tips:

  • Set innodb_ft_min_token_size = 2; I don't think it is 'bad' idea. Note: If you are changing this value, do so in the config file, restart MySQL, then rebuild any FT indexes. (Otherwise, the "2" won't take effect.)
  • Programmatically add a + in front of each word, and add IN BOOLEAN MODE.
  • Programmatically: Either remove 1-character 'words' from the search string, or at least don't add a + in front of them.

Here's a trick that is useful sometimes:

WHERE MATCH(col) AGAINST('+css 3' IN BOOLEAN MODE)
  AND col LIKE '%css 3%'

The MATCH will happen first (even if it is not first in the WHERE). It will find rows with the word "css", but ignore the "3". Then the LIKE will double check that there is exactly "css 3" somewhere in the string. Even this is imperfect, since "css 31" will be found.

Related Question