Changing the MySQL search minimum word length

PoetJC

⚧ Jacquii: Kween of Hearts ⚧
Joined
Jul 9, 2006
Messages
21,030
Hmmmm. I didn't know whether to post this in the server section or in Xenforo. But since the questions are mostly in reference to a Xenforo search option - I'll post it in the XF section... Okay.

Question 1: Is there any benefit to actually changing the MySQL search minimum word length to accommodate Xenforo's 'Search Minimum Word Length', which usually defaults to 4 characters?

Search Minimum Word Length
This is the minimum length of a word that can be searched by the index. With the default search system, this should correspond with the MySQL full text minimum word length (normally 4).

Question 2: Is there any downside to changing the option to say 3 instead of 4?
Thanks for all opinions.

J.
 

we_are_borg

Administrator
Joined
Jan 25, 2011
Messages
5,802
The minimum word lenght is by default 4 in mysql. If you want less or more this is what you'll need to add "ft_min_word_len = 3" the Xenforo should be the same value. After changing the word lenght you'll need to run "REPAIR TABLE my_table QUICK;" where my_table is the table Xenforo stores the search text, if you do not do this the old fields are not updated.

Xenforo and MySQL should have the same value, if not then you can get unexpected behavour if you set Xenforo to 3 and not MySQL then Xenforo can't store the index. If you reverse this MySQL can store but Xenforo will not index because of the setting. Keeping it the same is the smartest thing to do.
 

PoetJC

⚧ Jacquii: Kween of Hearts ⚧
Joined
Jul 9, 2006
Messages
21,030
The minimum word lenght is by default 4 in mysql. If you want less or more this is what you'll need to add "ft_min_word_len = 3" the Xenforo should be the same value. After changing the word lenght you'll need to run "REPAIR TABLE my_table QUICK;" where my_table is the table Xenforo stores the search text, if you do not do this the old fields are not updated.

Xenforo and MySQL should have the same value, if not then you can get unexpected behavour if you set Xenforo to 3 and not MySQL then Xenforo can't store the index. If you reverse this MySQL can store but Xenforo will not index because of the setting. Keeping it the same is the smartest thing to do.
I've read something very similar to your post during my research. And I understand all of that.
But what I'm asking is whether there's any upside or downside to changing the value... Like will decreasing the value to 3 produce better search results? Will decreasing the value have much negative or positive impact on server performance?...

Thanks,

J.
 

we_are_borg

Administrator
Joined
Jan 25, 2011
Messages
5,802
Well it depends if you have a site like TAZ i would go for 3 my self or even 2 because else you can not search for PHP, JS, C# etc. The problem you will have is common words, you'll need to block does so words like "an", "the" etc are not indexed. But if needed its better to look at the search addon Xenforo has, it should be better.

As for speed decreasing the value will result in more hits so you expect lower preformance, but you can always switch to the search addon that should be much faster. Under vBulletin Digitalpoint had a great search solution, i have no idea if he has that solution under Xenforo. That search addon was fast really fast.
 

PoetJC

⚧ Jacquii: Kween of Hearts ⚧
Joined
Jul 9, 2006
Messages
21,030
I'd mentioned DP's search add-on before. Hmmm... And I believe we have a premium membership still active there. Great suggestion. As for adding common words to a block-list of sorts. Do you know where that option is in Xenforo? I don't even see it in search options. I wonder if it requires Elastic Search... Hmmm... It's just that an enhanced type of search functionality has been requested and we want to *ideally* provide it. What to do what to do??!!

J.
 

we_are_borg

Administrator
Joined
Jan 25, 2011
Messages
5,802
I think the common words are inside a php file. But if you go for DP search i think it has that coverd in the backend.
 

Xon

Developer
Joined
Feb 15, 2015
Messages
309
AFAIK, DP search for XenForo requires Elastic Search.
 

we_are_borg

Administrator
Joined
Jan 25, 2011
Messages
5,802
AFAIK, DP search for XenForo requires Elastic Search.

Yes XF or DP NEEDS elastic search you'll need at least a VPS. Shared hosting would not allow you to use those addons , but it would also not allow to change the setting.
 

mysiteguy

Migration Expert
Joined
Feb 20, 2007
Messages
3,468
Common words are not indexed by default by mysql. They are in the default stop word file. If you want to add additional common words not to index you can give mysql a defined stopword file in my.cnf.
 

R0binHood

Habitué
Joined
Nov 23, 2011
Messages
1,602
After changing the word lenght you'll need to run "REPAIR TABLE my_table QUICK;" where my_table is the table Xenforo stores the search text, if you do not do this the old fields are not updated.

What is the my_table table name that I need to substitute in for a XenForo install?
 

R0binHood

Habitué
Joined
Nov 23, 2011
Messages
1,602
I just tried to use the built in rebuilder in the tools section oft he ACP. Got errors though, so I put a post up on XF.
 
Top