I’m trying to create a FULLTEXT index on an attribute of a table. Mysql returns
ERROR 1214: The used table type doesn’t support FULLTEXT indexes.
Any idea what I’m doing wrong?
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
You’re using the wrong type of table. Mysql supports a few different types of tables, but the most commonly used are MyISAM and InnoDB. MyISAM (in MySQL 5.6+also InnoDB tables) are the types of tables that Mysql supports for Full-text indexes.
To check your table’s type issue the following sql query:
SHOW TABLE STATUS
Looking at the result returned by the query, find your table and corresponding value in the Engine column. If this value is anything except MyISAM or InnoDB then Mysql will throw an error if your trying to add FULLTEXT indexes.
To correct this, you can use the sql query below to change the engine type:
ALTER TABLE <table name> ENGINE = [MYISAM | INNODB]
Additional information (thought it might be useful):
Mysql using different engine storage types to optimize for the needed functionality of specific tables. Example MyISAM is the default type for operating systems (besides windows), preforms SELECTs and INSERTs quickly; but does not handle transactions. InnoDB is the default for windows, can be used for transactions. But InnoDB does require more disk space on the server.
Method 2
Up until MySQL 5.6, MyISAM was the only storage engine with support for full-text search (FTS) but it is true that InnoDB FTS in MySQL 5.6 is syntactically identical to MyISAM FTS. Please read below for more details.
InnoDB Full-text Search in MySQL 5.6
Method 3
On MySQL <= 5.5, the mysql manual says that FULLTEXT indexes can only be created on tables with the mylsam engine.
Method 4
Are you using InnoDB? The only table type that supports FULLTEXT is MyISAM.
Method 5
apart from MyISAM table PARTITIONING also not support full-text index.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0