HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

MySQL fulltext index


#1

I saw in the 4.6.8 release notes that support has been added for using MySQL’s fulltext indices for history search, and can see the change in the table structure on my installation. But I am curious what sort of configuration changes are needed to move from using Sphinx to the MySQL fulltext search.

I like Sphinx, but welcome the elimination of the dependency, as it introduces a bit more complexity into the server configuration.

I commented out the Sphinx configuration in the config.php file (cSEARCHHOST and cSEARCHPORT), but when I tried that, I got very strange search results (it looked almost like a list of all recent requests regardless of whether or not they contained matching text).

Is there another configuration option required to take advantage of the MySQL indices? Or is the feature still in the works?

Thanks!

N


#2

Hey there, yes there is another setting needed! You will need to add a new parameter to your config.php

define('SEARCH_ENGINE', 'database');

That should switch you over to database search.


#3

Hmm, that didn’t seem to work as expected. I added and am still getting a list of recent requests when trying to use “Search History” from the sidebar. I also tried the following, which seems more in keeping with the other configuration definitions, to no avail:

define('cSEARCH_ENGINE', 'database');

Thanks!

N


#4

The original setting name that I posted is correct. What kind of search are you using in the search history area? The mysql search used by default in that box is a natural language match. The natural language search will find single word matches in a multi word string. So for example, if I search for HelpSpot Responses results with only HelpSpot and only Responses will also be returned. MySQL will give a weight to each match, however we are returning the results ordered by recency since the most recent results are probably the ones someone is looking for through quick search.

You can also do a “boolean” search in the quick search area. When we detect boolean operators in the search we automatically switch to that type of search. So for example if I search for +HelpSpot +Responses then only results with both HelpSpot and Responses will be returned.

Let me know if this sounds like the behavior you are seeing of if you are getting something different.


#5

Okay, I think there is a bug. The configuration constant you gave does work if I go to the search page and switch to the “Full Text” tab, but it does not work when using the “Search History” checkbox in the sidebar.

Below is an image comparing the results using Sphinx and the sidebar search box (with “Search History” checked), and the same search using the configuration constant you provided. It is a one word query. The configuration constant causes all recent requests to be listed, regardless whether or not they contain the matching term.


#6

Could you tell me what the one word query is? If you prefer, feel free to open up a request at https://support.helpspot.com and we can take a look at this privately as well. We may need to take a look deeper at your particular database. Thanks!


#7

It is irrespective of the query word. Any query I type in the sidebar box with the “SEARCH_ENGINE” configuration set to “DATABASE” will return all recent requests. For example, I search for “tomato” with Sphinx, it returns no results. If I search for “tomato” with the configuration setting you recommended, it shows the fifty most recent requests in my installation.

But as mentioned above, the configuration works great if I go to the search page and use the “Full Text” search.

Let me know if you want me to open a ticket to discuss further.

N


#8

Yes, I think we should open a ticket on this. I haven’t been able to recreate this at all (we’ve been using db full text search in our own production installation). There may be something more specific to your DB that we need to look at and I may need to bring in some of our development team to have a look.


#9

Done! Thank you Matt.


#10

For others that may happen across this thread. This is a bug in HS 4.6.8. It will be patched in HS 4.6.9