Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New Coder
    Join Date
    Oct 2016
    Posts
    36
    Thanks
    6
    Thanked 1 Time in 1 Post

    FULLTEXT search in innoDB database

    I have reasently switched my databse from MyiSam to innoDB because of access to foreign keys (I have about 150 tables where most of them are referencing each other and foreign keys just makes my life so much easier)

    One problem though - it broke my FULLTEXT searches and a search like:
    PHP Code:
    SELECT 
        date 

        
    username 
        
    bio 
        
    randomString 
    FROM 
        userlogininfo 
    WHERE 
        MATCH 
    username bio AGAINST ( ? IN BOOLEAN MODE 
    ORDER BY 
        date DESC 
    giving a list of users matching the search criteria in ? now throws a "#1191 - Can't find FULLTEXT index matching the column list"

    And both username (TEXT) and bio (TEXT) has FULLTEXT indexes
    Allthough a:
    PHP Code:
    SHOW INDEX FROM userlogininfo
    shows that not only does both username and bio have FULLTEXT indexes, but so do username_2 and bio_2 (none of which are named columns in the table) - don't know if that matters, but maybe?

    But like I said - where the above user search in MyiSam worked fine - in innoDB they throw a "#1191 - Can't find FULLTEXT index matching the column list"

    I have tried reading the MySQL documentation and looking on different forums (incl. this one) but all proposed solutions are on how to add the FULLTEXT index for people who didn't already (tried adding FULLTEXT again using the suggested ALTER TABLE in different answers - but none of them did anything and it still throws the same error #1191)

    Anybody have any idea what to do to make my FULLTEXT searches work again? (because I would really like to avoid loosing the foreign key functionality going back to MyiSam)

    userlogininfo
    Code:
    ------------------------------------------
    | id               | username | bio      |
    ------------------------------------------
    | INT(11) NOT NULL | TEXT     | TEXT     |
    | AUTO_INCREMENT   | FULLTEXT | FULLTEXT |
    ------------------------------------------

  2. #2
    Regular Coder Vege's Avatar
    Join Date
    Jan 2008
    Posts
    988
    Thanks
    22
    Thanked 134 Times in 133 Posts
    Is mysql version >= 5.6?
    Innodb full text was introduced in 5.6

  3. #3
    New Coder
    Join Date
    Oct 2016
    Posts
    36
    Thanks
    6
    Thanked 1 Time in 1 Post
    MySQL-server version: 5.7.14

  4. #4
    New Coder
    Join Date
    Oct 2016
    Posts
    36
    Thanks
    6
    Thanked 1 Time in 1 Post
    Ok after spending the last 3 days reading every single page on https://dev.mysql.com/doc/refman/5.7/en/ - I finally found out the problem - so I'll post it here in case anybody has the same problem and is looking for a solution.

    The problem (and thereby solution) is actually very simple.

    Where MyiSam databases allow you to search as many FULLTEXT columns as you like - innoDB only lets you search one at the time (don't ask me why - that's just the hand we were dealt).

    This means that in the above search:
    PHP Code:
    MATCH username bio AGAINST ( ? IN BOOLEAN MODE 
    in innoDB it is only able to find solutions for the first match column (ie. username) and the second (ie.bio) - since it can't be registrered as a FULLTEXT column because username already filled that position - is then throwing the error.

    So the solution in innoDB becomes spliting them up with an OR and all is now fine - and giving the results it should:
    PHP Code:
    WHERE
        MATCH 
    username AGAINST ( ? IN BOOLEAN MODE )
        OR
        
    MATCH bio AGAINST ( ? IN BOOLEAN MODE )
    ORDER BY.... 
    Last edited by Thothlike; Dec 22nd, 2017 at 01:51 PM.


 

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •