HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

MySQL Full Text Search Problem


#1

I’ve had this problem before but at that time, I couldn’t search any open/closed request.

I recently tried searching for an old, closed request using search. I have always used the name of my servers with tickets so I searched for wcs-as1 and nothing came up. Upon further manual searching (scrolling through all closed tickets), I found the closed ticket and sure enough it had the word “wcs-as1” in the body but for some reason it wasn’t turning up in the search.

I re-opened the request and did another search but nothing came up. Not sure what else might not be searchable but I did some quick tests for things like printers and monitors and everything seems to show.

Any ideas why wcs-as1 wouldn’t turn up in a search? Could it have something to do with the hyphen?

I just finished upgrading to 1.5.1 but this was an issue prior to the upgrade. I’m running Win2k3,MySQL 4.1.21,PHP 5.1.6, Zend 3.0.1


#2

Hi Tyson,

Yes, the MySQL fulltext engine breaks up the word at the hyphen. So it views wcs and as1 as different words. However, the fulltext engine also ignores words less than 3 characters so you can’t search for wcs by itself.

That word count is configurable though, so if you want to you can lower it and then rebuild the index and you’ll be able to search.

Also there are some fixes mentioned at the link below on how to make MySQL not treat the hyphen as a word break, but they’re not for the faint of heart.

http://bugs.mysql.com/bug.php?id=2095


#3
  1. Checked my current setting

mysql> SHOW VARIABLES LIKE ‘ft_min_word_len’;
±----------------±------+
| Variable_name | Value |
±----------------±------+
| ft_min_word_len | 4 |
±----------------±------+

  1. Added the following to my.ini

Minimum word length to be indexed by the full text search index.

You might wish to decrease it if you need to search for shorter words.

Note that you need to rebuild your FULLTEXT index, after you have

modified this value.

ft_min_word_len = 3

  1. Reloaded MySQL service

  2. Checked setting again

mysql> SHOW VARIABLES LIKE ‘ft_min_word_len’;
±----------------±------+
| Variable_name | Value |
±----------------±------+
| ft_min_word_len | 3 |
±----------------±------+

Now I need to rebuild the indexes. Ian, can you suggest how to rebuild the indexes using phpmyadmin and also which tables to rebuild? Thanks for your help!


#4

No problem. To rebuild the index you need to go to each table below and drop the fulltext index on the field. Then add a fulltext index back onto the field.

HS_Request_History.tNote
HS_Forums_Posts.tPost
HS_KB_Pages.tPage


#5

I opened hs_request_history and under Indexes at the bottom I clicked the red X to remove the index for tNote.

Then, under the Field column, I clicked the Fulltext button under the Action column.

Now, under Indexes, it lists the following:

PRIMARY
IndexxRequest
tNote

Is this correct? If so, I’ll do the same to the other tables you listed above.


#6

One thing that kind of scared me was the label of the index as “IndexxtNote” and after deleting it and recreating it, the label was just “tNote” and I didn’t know if that was right.

Everything appears to be working now Ian. Thank you for the ideas and assistance.

We can work within the 3 character minimum limit for search queries I’ve now set.


#7

The name shouldn’t be a problem, I don’t expect any changes to those indexes that would require HelpSpot to actually know the name.

Great, glad to see it’s working. Let me know if you hit any trouble.