Fulltext index not returning new records

[Katerine]

Hi,
I have an odd issue, that really needs fixing before tomorrow, and I've tried everything I can think of.

I have a MySQL database on a shared server (so I don't have administrative permissions), that has a table called `CONTENT_Chemicals`. This table currently has 25548 rows. It has 3 indexes (counting the PK), one of which is ChemFullText, a fulltext index of (ChemicalName, CommonName) (in that order). ChemicalName and CommonName are not listed in either of the other two indexes.

This query returns three results:

SELECT * FROM CONTENT_Chemicals WHERE ChemicalName LIKE '%better%';

This query returns no results:

SELECT * FROM CONTENT_Chemicals WHERE Match(ChemicalName, CommonName) AGAINST ('better');

Interestingly, this problem only applies to rows that have been added in the last week. For example, this query returns 34 results, all of which were added on Jan 17:

SELECT * FROM CONTENT_Chemicals WHERE Match(ChemicalName, CommonName) AGAINST ('fertilizer');

I've tried deleting the index (using the MySQL Workbench) and rebuilding it, with no luck. Anything else I can try (again, keeping in mind that I don't have administrative permissions to the server)?

Update: I've now tried copying all of the data to a second table, dropping the original table, recreating the original table (with only the PK index), copying the data back, and then recreating the other two indexes (including the Fulltext index). No luck. :-(

Katie

 

[Katerine]

Update: still no luck with anything I've tried (listed above). If anybody has any ideas, please help! Thanks...

Katie

 

[Katerine]

Update:
Ok, the problem, bizarrely, seems to only occur with some words.

This query returns the following result (among others):
Code:

SELECT * FROM CONTENT_Chemicals WHERE Match(ChemicalName, CommonName) AGAINST ('degreaser');

Result:

# ChemicalID, ChemicalName, CommonName, DateCreated, DateModified
'34054', 'ITS BETTER DEGREASER', NULL, '2017-01-17 18:19:10', NULL

...but this query still returns no results:

SELECT * FROM CONTENT_Chemicals WHERE Match(ChemicalName, CommonName) AGAINST ('better');

What the heck?

To reiterate, the table now has 25763 rows, and the "better" query should return 3 rows, so it's not the "50% or more" issue, which is all I've found online. So what could it be?
Katie

 

[Katerine]

Update:
Found the problem, not the solution. Apparently, "better" is a full-text stopword. Found here:

https://dev.mysql.com/doc/refman/5.5/en/fulltext-stopwords.html

This sucks. No way is my host going to agree to edit the storage/myisam/ft_static.c file, because that could mess up some of their other clients. I don't suppose anybody knows of a way to fix searches so that they allow the stopwords, or a workaround? A lot of the default stopwords in that list are words that I don't want to be stopwords.

Katie

 

[ChrisHirst]

https://dev.mysql.com/doc/refman/5.7/en/fulltext-stopwords.html

run your FULLTEXT search using IN BOOLEAN MODE

https://dev.mysql.com/doc/refman/5.5/en/fulltext-boolean.html

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.

Never mind this jesus character, stars had to die for me to live.

 

[Katerine]

ChrisHirst:
Unfortunately, I'm on a shared MySQL server. I can't just change the engine from MyISAM to InnoDB, nor can I change the storage/myisam/ft_static.c file.

The problem persists when running the searches in boolean mode - in fact, my original queries that the client was talking about were in boolean mode. :-( This, for example, does not work (my code automatically adds pluses to all search terms because the client specifically requested that searches be exclusive, not inclusive... that's mandatory):

SELECT * FROM CONTENT_Chemicals WHERE Match(ChemicalName, CommonName) AGAINST ('+better' IN BOOLEAN MODE);

...returns 0 results, when it should return 3.

Katie

 

[Katerine]

In case anybody else has a similar problem, thought I should share the workaround. It really was a workaround, thanks to being on a shared server. It was pretty complicated, and my code is kind of difficult to read, so I'll just sum up the logic. Basic gist is, if a search term is less than 4 characters, or if it's in the array of stopwords that I provided, then put it in an array to be used in a REGEXP condition. Otherwise, put it in a string to be used in a fulltext search, and do both REGEXP (if there's anything in the array) and the MATCH (if there's anything in the fulltext boolean string) in the SQL query.

Katie