MySQL FULLTEXT indexes issue

Question

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?

score 55 · Accepted Answer · edited Mar 15 '14 at 14:44

55

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.

edited Mar 15 '14 at 14:44

tolsam

25
6

answered Jun 08 '09 at 05:43

Cimplicity

2,722
2
25
26

No, but the ALTER TABLE statement says "MYISM". – Henning Jun 08 '09 at 05:57
Sorry to bug you again, but the bit you added at the end at least needs clarification. MyISAM does not support transactions. And why would MyISAM be better for updates? It locks every row it touches on updates, and while it performs well in heavy read/low write scenarios, it fails in heavy write scenarios. – Henning Jun 08 '09 at 20:52
I was going from memory on that part... I'll look it up again and if I was wrong change it shortly... Thanks for heads up again. Perhaps I got the two swapped in pros and cons. – Cimplicity Jun 08 '09 at 22:36
Thanks a lot... I solved the problem which is same with this. – Jun 15 '12 at 17:58
MYISAM also doesn't support foreign keys, IIRC. – Jamon Holmgren Jan 15 '13 at 18:24
OK, what about all your tables are Myisam, and this query worked on your views before, but since upgrading mysql, this suddenly doesn't work on those same "views"? – SpYk3HH Feb 05 '14 at 15:20

score 10 · Answer 2 · answered Mar 06 '13 at 13:33

10

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

answered Mar 06 '13 at 13:33

Sukhjinder Singh

1,745
2
19
26

score 7 · Answer 3 · edited Jul 01 '21 at 12:21

7

On MySQL <= 5.5, the mysql manual says that FULLTEXT indexes can only be created on tables with the mylsam engine.

edited Jul 01 '21 at 12:21

Lucas Bustamante

15,821
7
92
86

answered Jun 08 '09 at 05:42

soulmerge

73,842
19
118
155

2

This is no longer true. – jcoffland Nov 04 '14 at 19:45

score 5 · Answer 4 · answered Jun 08 '09 at 05:43

5

Are you using InnoDB? The only table type that supports FULLTEXT is MyISAM.

answered Jun 08 '09 at 05:43

Artem Russakovskii

21,516
18
92
115

1

This is no longer true. – jcoffland Nov 04 '14 at 19:46

score 3 · Answer 5 · answered Mar 25 '16 at 14:19

3

apart from MyISAM table PARTITIONING also not support full-text index.

answered Mar 25 '16 at 14:19

Saurabh Chandra Patel

12,712
6
88
78

MySQL FULLTEXT indexes issue

5 Answers5

Linked