How to create Full-text search index in MySQL AWS Aurora DB InnoDB engine

Question

While trying to add full-text index on the table I am getting below error.

Error Code: 1214. The used table type doesn't support FULLTEXT indexes

And while trying to create a table with MyTSAM engine I am getting below warning message.

0 row(s) affected, 1 warning(s): 1266 Using storage engine InnoDB for table

Answer 1

AWS Aurora supports full text indexing. We're using it with InnoDb (MySQL 5.7) tables like:

CREATE TABLE full_search_indexes (
  code varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  entity_type varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  full_text text COLLATE utf8mb4_unicode_ci,
  time_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  time_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (code,entity_type),
  FULLTEXT KEY full_text (full_text)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;