0

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?

  • What is MySQL version(x,y,z)? – Md Haidar Ali Khan Nov 4 '18 at 5:49
  • I've requested for my hosting provider to upgrade to the latest version. I'm guessing it'll be 8.x. – DatabaseNewbie Nov 4 '18 at 8:09
0

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.

  • Will the stop word still be respected during indexing? How does this successfully find css 3? – DatabaseNewbie Nov 4 '18 at 23:13
  • @DatabaseNewbie - You may need to specify that there is no stopword list. (I don't know the details; see the manual.) And, yes, AGAINST('+css 3' IN BOOLEAN MODE) will find all rows with the word 'css'. Alas, it will also find rows with 'css 2'. – Rick James Nov 4 '18 at 23:22
  • Wouldn't' the 3 be excluded by the indexing? And when a user searches for css 3, I want to give more relevance to css 3 than css. Is that what would happen? – DatabaseNewbie Nov 4 '18 at 23:25
  • @DatabaseNewbie - No. The "3" is ignored. Can't give more relevance in this example. I added some more to my Answer. – Rick James Nov 4 '18 at 23:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.