XF 1.5 Search table innodb does not work properly

[Dakis]

I've swapped my xf_search_index table to innodb and now some searches return zero results (even though there are definitely topics with the relevant keywords in the forum).

I tried rebuilding the search index and that didn't fix the problem. I tried doing it again and checking the "delete index" option and that didn't fix it either. One thing I noticed though is that when I tried to rebuild the search it starts from post 216.023 , I'm not sure if that means anything?

Any ideas?

Edit: these searches that come up empty are the ones that include 2-letter and 3-letter words (but they also include larger words). Before changing my table to innodb this was working. For example searchin for "Pirates of the Caribbean" was returning results before, but now returns zero results.

 

[Mike]

InnoDB full text search has different behaviors than MyISAM full text search. We can only officially support the (default) MyISAM-based search.

 

[Dakis]

Thanks Mike, I wasn't aware of this. Will it make a difference if I buy the enhanced search addon?

Are there any other members that can help me with this problem?

 

[Mike]

Enhanced Search uses Elasticsearch for the searching (so you must have an Elasticsearch server), so it uses totally different technology to the do the searching. You can try it out here.

 

[Dakis]

Ok thanks Mike.

At the moment I am trying to find out what the issue is, as it is only happening on one of my sites - the others seem to be working just fine.

If anyone else has encountered this type of issues please give me a shout.

 

[Chris D]

I would honestly just put the xf_search_index table back to MyISAM. The default for the majority of the XF tables is InnoDB. There's a small number that are MyISAM. And some that are MEMORY. The ones that aren't InnoDB are like that for a reason so it is recommended that they stay using the storage engines we assigned to them originally.

 

[Dakis]

Thanks for the reply Chris, I might do that in the end.

 

[jeffwidman]

This doesn't smell right... AFAIK, the SQL queries for index creation and full-text searching on InnoDB vs MyISAM shouldn't change because the choice between InnoDB vs MyISAM is generally an internal implementation detail. Something you can tune, certainly, but not something that changes the So if there's an issue, it's most likely a MySQL/MariaDB bug, not specific to Xenforo issue.

The only Xenforo specific MyISAM option that I'm aware of is the 'Delayed Insert' option--when I converted my xf_search_index table over to InnoDB, I just disabled that option, and everything worked fine as far as I could tell.

That said, for any forum of significant size ElasticSearch is generally a better way to go... doesn't cost that much, relatively easy to setup, returns more relevant results, and fully supports queries of less than 4 letters (like searching for 'USC').

 

[AndyB]

Hi Dakis,

With your size of forum you should be using Enhanced Search.

 

[Dakis]

Wouldn't enhanced search require more resources from the server?

 

[rdn]

Wouldn't enhanced search require more resources from the server?

For 700K post forum:

 

[Mike]

AFAIK, the SQL queries for index creation and full-text searching on InnoDB vs MyISAM shouldn't change because the choice between InnoDB vs MyISAM is generally an internal implementation detail.

Syntactically they are the same, but we have had reports of errors happening only in the InnoDB version (particularly in relation to somewhat malformed queries). It's certainly possible that has been tweaked in more recent versions of InnoDB, though I'm not sure.