INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Fulltext index not returning new records

Fulltext index not returning new records

(OP)
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:

CODE --> MySQL

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

This query returns no results:

CODE --> MySQL

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:

CODE --> MySQL

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. sad

Katie

RE: Fulltext index not returning new records

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

Katie

RE: Fulltext index not returning new records

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

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

CODE --> sql

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

CODE -->

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

...but this query still returns no results:

CODE --> sql

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

RE: Fulltext index not returning new records

(OP)
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-s...

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

RE: Fulltext index not returning new records

(OP)
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. sad 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):

CODE --> sql

SELECT * FROM CONTENT_Chemicals WHERE Match(ChemicalName, CommonName) AGAINST ('+better' IN BOOLEAN MODE); 
...returns 0 results, when it should return 3.

Katie

RE: Fulltext index not returning new records

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close