MySQL InnoDB FULLTEXT Indices Don't Work as Expected

[Ghan_04]

Title.
I'm so frustrated with this right now because all the documentation I've seen says that the syntax of MyISAM vs. InnoDB fulltext searching is exactly the same. This is demonstrably false.
I've been working on an issue where multi-word searching on xf_search_index hasn't been returning any results. Here is the behavior:

ON MYISAM:

Search for the text '[Shards of Immortality]' returns threads with that in the title. There are many threads with that title - on our roleplaying board, it's how we've been archiving old roleplays.

If I run the following query directly against the database, it returns the correct results:

SELECT * FROM xf_search_index WHERE MATCH (title) AGAINST ('[Shards of Immortality]' IN BOOLEAN MODE)

If I search WITHOUT brackets, I also get the expected results.


ON INNODB:

Search for the text '[Shards of Immortality]' returns NO RESULTS.

If I run the following query directly against the database, it returns NOTHING:

SELECT * FROM xf_search_index WHERE MATCH (title) AGAINST ('[Shards of Immortality]' IN BOOLEAN MODE)

However:

If I run the search directly against the database WITHOUT brackets, I DO get results.
If I run the search on xenForo WITHOUT brackets, I DO NOT get results.

Sense? None.
It would be nice if this were something that I'm missing and being really stupid about, but it doesn't seem like it from the testing I've done. Any suggestions are welcome, though I'm not hopeful of any besides reporting this as a MySQL bug.

P.S. Rebuilding the search index in the admin panel does nothing.

 

[Jake Bunce]

I haven't tested the fulltext search with InnoDB. XenForo uses MyISAM for the xf_search_index table. You should leave it as MyISAM. For very busy forums you should consider using the Enhanced Search addon instead of fulltext which doesn't scale well.

 

[Ghan_04]

I know fulltext doesn't scale well. This isn't for performance - I'm just trying out the new software.
Why would you prefer MyISAM?

 

[Deebs]

Read this: http://www.mysqlperformanceblog.com...-text-search-in-mysql-5-6-part-2-the-queries/

 

[Slavik]

Read this: http://www.mysqlperformanceblog.com...-text-search-in-mysql-5-6-part-2-the-queries/

That seems like a total pain in the arse to work with!

I know fulltext doesn't scale well. This isn't for performance - I'm just trying out the new software.
Why would you prefer MyISAM?

MyISAM isn't "bad" to use. All the Digitalpoint tables run on it.

 

[nrep]

MyISAM isn't "bad" to use. All the Digitalpoint tables run on it.

There are some huge benefits of using InnoDB for most people - no table locks, snazzy backup methods, etc... I wouldn't use MyISAM in most cases, although Digitalpoint no doubt has the experience and hardware to get around some of the pitfalls of MyISAM that effect others. For most people, I'd recommend InnoDB anyday.

I'd really like to see if anyone does get InnoDB fulltext search working - as there's no reason why it shouldn't. Hopefully the performance will be adequate for small/medium size sites.

 

[Slavik]

There are some huge benefits of using InnoDB for most people - no table locks, snazzy backup methods, etc... I wouldn't use MyISAM in most cases, although Digitalpoint no doubt has the experience and hardware to get around some of the pitfalls of MyISAM that effect others. For most people, I'd recommend InnoDB anyday.

I'd really like to see if anyone does get InnoDB fulltext search working - as there's no reason why it shouldn't. Hopefully the performance will be adequate for small/medium size sites.

Yes, InnoDB does provide benefits, which most people are able to utilise, so they should, I never said otherwise.

However thats not to say MyISAM is "bad" at all, which is what a lot of people seem to push.

 

[nrep]

Yes, InnoDB does provide benefits, which most people are able to utilise, so they should, I never said otherwise.

However thats not to say MyISAM is "bad" at all, which is what a lot of people seem to push.

Likewise I never said MyISAM was bad .

Anyway, I don't want to steer this thread off topic. It should be possible to get fulltext InnoDB search working, I've had it working with VB after some tweaking, so I'll give it a go on my test XF installation and see if I can get it working. If it performs close to MyISAM fulltext search for small/medium boards, then it would be worth using.

 

[Jafo]

Sorry to bump this up, but I found this while researching a conversion I am doing from MyISAM to Innodb. Have you checked the stop words for Innodb and how they may be affecting your query? I understand there is a considerable difference between the two.

 

[Ghan_04]

That might be helpful for the xenForo devs, but not so much for me unless there is something I'm missing here - this is something that needs to be fixed in the software itself. And for all I know, it might have been fixed in xenForo 1.2, though I haven't heard one way or the other. I have not experimented since then.

 

[nrep]

If it performs close to MyISAM fulltext search for small/medium boards, then it would be worth using.

I forgot about this until recently, so I implemented it this morning and it seems to work fine. I've converted all table types to InnoDB (except MEMORY ones) and the search/session tables work fine .