2019-07-19, 07:50 PM
Hi, in MyBB version 1.8.21... in ACP > Settings > Search System > Search Type, it explains there are 2 choices for Search:
Our forum had always been set to "Full Text" because of this recommendation, and yet search had never worked correctly. It turns out that when you choose "Full Text", you are using the MySQL search functions related to "Fulltext Index" of the table columns:
Threads > Subject
Posts > Subject
Posts > Message
But what the description doesn't tell you (other than the hint, "depending on your database"), is that MySQL comes with a built-in set of "stopwords" which are omitted from search... So it's not really "Full Text". The wisdom at MySQL is that some words are "too common" to be useful. If your MySQL table type is "MyISAM", then the stopword list is 500+ words... This effectively makes search useless, and is exactly what we were experiencing.
In trying to fix this, the first thing I came across was MySQL's own information that if you change your Tables from MyISAM to InnoDB, then the stopword list is only 36 words... but even then, among those 36 words are "what, when, why, where, who, how..." and our forum is all about questions - so it still makes search results effectively useless for us.
There are instructions on how to configure your MySQL to either ignore stopwords, or create your own stopword file with nothing in it, to override the built-in default list - and then rebuild your fulltext indexes (an important step...). However, if your forum is on a shared server at a hosting company, as most people's are, then it seems impossible to actually change the MySQL stopword behaviour. All of the instructions are done at the server "Global" level, and require restarting MySQL - neither of which hosting companies will do because it affects all users on the shared server. There are clear instructions to change settings for a "Session"... but as soon as you execute the command, the "Session" is over, and it reverts to the Global settings.
There is a "my.cnf" file in the top level of my user account, and there are instructions on how to override stopwords by adding code in this file - but none of the variations suggested actually work, apparently. My host says this file gets read every several hours for user preferences, but every variation of this concept doesn't seem to work for me:
So I have 2 questions...
1. Does anyone know how to reliably turn off stopwords in MySQL for a single user in a shared server environment? When I go to PHPMyAdmin and execute an SQL query:
I get the system variable for ft_stopword_file = (built in)
[attachment=42006]
2. My only solution to fix MyBB search, was to go to ACP and revert to "Standard" search... abandoning the enticing invitation to use the "more powerful, faster Full Text"... So what is different in Standard Search? I gain the ability to search for all words, I think, so what is "better" about full text searching, what do I miss by using Standard? How do I actually GET Full Text searching??
Thanks.
Quote:Please select the type of search system you wish to use. You can either chose between "Standard", or "Full Text" (depending on your database). Fulltext searching is more powerful than the standard MyBB searching and quicker too.
Our forum had always been set to "Full Text" because of this recommendation, and yet search had never worked correctly. It turns out that when you choose "Full Text", you are using the MySQL search functions related to "Fulltext Index" of the table columns:
Threads > Subject
Posts > Subject
Posts > Message
But what the description doesn't tell you (other than the hint, "depending on your database"), is that MySQL comes with a built-in set of "stopwords" which are omitted from search... So it's not really "Full Text". The wisdom at MySQL is that some words are "too common" to be useful. If your MySQL table type is "MyISAM", then the stopword list is 500+ words... This effectively makes search useless, and is exactly what we were experiencing.
In trying to fix this, the first thing I came across was MySQL's own information that if you change your Tables from MyISAM to InnoDB, then the stopword list is only 36 words... but even then, among those 36 words are "what, when, why, where, who, how..." and our forum is all about questions - so it still makes search results effectively useless for us.
There are instructions on how to configure your MySQL to either ignore stopwords, or create your own stopword file with nothing in it, to override the built-in default list - and then rebuild your fulltext indexes (an important step...). However, if your forum is on a shared server at a hosting company, as most people's are, then it seems impossible to actually change the MySQL stopword behaviour. All of the instructions are done at the server "Global" level, and require restarting MySQL - neither of which hosting companies will do because it affects all users on the shared server. There are clear instructions to change settings for a "Session"... but as soon as you execute the command, the "Session" is over, and it reverts to the Global settings.
There is a "my.cnf" file in the top level of my user account, and there are instructions on how to override stopwords by adding code in this file - but none of the variations suggested actually work, apparently. My host says this file gets read every several hours for user preferences, but every variation of this concept doesn't seem to work for me:
[mysql]
ft_stopword_file=""
[mysqld]
ft_stopword_file=""So I have 2 questions...
1. Does anyone know how to reliably turn off stopwords in MySQL for a single user in a shared server environment? When I go to PHPMyAdmin and execute an SQL query:
Quote:show variables like 'ft_%';
I get the system variable for ft_stopword_file = (built in)
[attachment=42006]
2. My only solution to fix MyBB search, was to go to ACP and revert to "Standard" search... abandoning the enticing invitation to use the "more powerful, faster Full Text"... So what is different in Standard Search? I gain the ability to search for all words, I think, so what is "better" about full text searching, what do I miss by using Standard? How do I actually GET Full Text searching??
Thanks.