1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL InnoDB FULLTEXT Indices Don't Work as Expected

Discussion in 'Troubleshooting and Problems' started by Ghan_04, Mar 7, 2013.

  1. Ghan_04

    Ghan_04 Active Member

    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:

    Code:
    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:

    Code:
    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.
     
  2. Jake Bunce

    Jake Bunce XenForo Moderator Staff Member

    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.
     
  3. Ghan_04

    Ghan_04 Active Member

    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?
     
  4. Deebs

    Deebs Well-Known Member

  5. Slavik

    Slavik XenForo Moderator Staff Member

  6. nrep

    nrep Well-Known Member

    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.
     
    Adam Howard likes this.
  7. Slavik

    Slavik XenForo Moderator Staff Member

    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.
     
  8. nrep

    nrep Well-Known Member

    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.
     
  9. Jafo

    Jafo Active Member

    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. :)
     
  10. Ghan_04

    Ghan_04 Active Member

    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.
     
  11. nrep

    nrep Well-Known Member

    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 :).
     
    Adam Howard likes this.

Share This Page