In case this helps anyone else to resolve Search issues in MyBB 1.8.21, I was able to solve my problem.
Please note I am not a programmer, and I took some risk, after verifying this should work on my version, and it did.
Your Mileage May Vary.
SUMMARY:
The problem was that Search was returning zero results for a long list of common words (like "what", "today", etc.).
MySQL has a built in list of
stopwords which are omitted from the
fulltext index of tables
when the index is created.
That last part is important - lots of advice tells you how to turn off the stopwords, but you also have to reindex.
There are 2 common sets of default stopwords, depending how old your MySQL is:
MySQL Table Type used to be
MyISAM - which had 500 or so stopwords (making search useless).
MySQL version 5.6 and above, Table Type
InnoDB is an option - which only has 36 stopwords.
MySQL 5.6 Fulltext stopwords
I discoverd mine were the MyISAM simply by searching for the words in the large list - all returned zero search results.
The solution was to convert all MyBB's MySQL tables to
InnoDB, and then recreate the fulltext indexes on 3 table columms:
mybb_posts > subject
mybb_posts > message
mybb_threads > subject
You should read up on the differences between the 2 table types in MySQL and the implications on search, they both have pros and cons, apparently. However, it seems that MyISAM is on its way out, and InnoDB is the new preferred type.
My installation of MyBB is on a shared server at a large hosting company, Apache, PHP version 7.1.14, MySQL version 5.6.14
STEPS TO FIX MY SEARCH in MYBB:
1. I logged in to MyBB Admin (ACP) to check what versions of MyBB, MySQL and PHP I was running.
2. I logged in to my cPanel > PHPMyAdmin > mybbdatabase
3. I converted
EVERY table >by doing this... Selecting each table one at a time:
For each table, clicked on Operations tab > Table Options > changed the Storage Engine to
InnoDB and clicked "Go"
4. I then clicked on the parent mybbdatabase on the left of PHPMyAdmin, then the SQL tab, and entered this code:
SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DEFAULT_STOPWORD;
That should result in a list of 36 words that will be omitted from the fulltext index when recreated for searches.
5. To force the Posts and Threads to reindex with the new, shorter stoplist, I entered the following in the SQL tab, which appears to be a simple way to trigger the tables to reindex the fulltext indexes:
alter table mybb_posts engine=innodb;
alter table mybb_threads engine=innodb;
6. Next, might have been unnecessary, but I went back to each table > Operations tab > Table Maintenance > Optimize table
The result is that now I can search for any word other than the 36 in the shorter default InnoDB stopwords list....
7. I wanted real full text search (no words omitted), so following advice on other forums:
• I created my own stopwords table
mybb_stopwords
• I added a single entry
stopwords1
• Set MySQL to use that as the stopwords reference table, and
• Finally recreated the indexes, now omitting only the single word.
In cPanel > PHPMyAdmin > mybbdatabase > SQL tab (note use your own database and table names, and single stopword entry...)
CREATE TABLE mybb_stopwords(value VARCHAR(30)) ENGINE = INNODB;
INSERT INTO mybb_stopwords(value) VALUES ('stopword1');
set innodb_ft_enable_stopword="on";
set session innodb_ft_user_stopword_table='mybbdatabase/mybb_stopwords';
alter table mybb_posts engine=innodb;
alter table mybb_threads engine=innodb;
This seemed to work. Now there were only 2 more limitations to search in MyBB:
MySQL has a default minimum search word length of 3 or 4. In the latest upgrade of
MyBB > ACP > Configuration > Settings > Search System, you can modifly the
Minimum Search Word Length. If you leave zero in the field, it defaults to whatever MySQL on your server is, 3 or 4. I put 2 and that works.
Last, there is advice out there that you can simply add code to the
my.cnf file at the
root of your server, which contains settings for MySQL, to turn off stopwords. However, on a shared server, you are unable to reboot MySQL for the change to take effect (unless your hosting provider agrees). My host advised me that any code in that file gets loaded periodically, but we'll see. Also, I think that even if you are able to turn off the stopwords via the my.cnf file, the fulltext indexes were already created WITH the stopwords, so you still have to reindex them, I think. Maybe someone else can clarify this?
I added this to
my.cnf and we'll see if it has any effect (but with the above changes, it may be moot):
ft_stopword_file=""
If anyone has any corrections or advice, please chime in. This worked for me - but I have to say I wish MyBB had a little more info in the introduction about how search is limited by whatever version of MySQL is on the server... this took a day for me to figure out, and I'm not even sure if it will work permanently. Thanks.