Working with full-text indexes

In order to perform full-text searches on a table, you must index the data. In MariaDB, the type of index used for full-text searches is named FULLTEXT.

A full-text index can only be created on a column of type CHAR, VARCHAR, or TEXT.

As with the other indexes, the FULLTEXT index can be created by using CREATE TABLE when creating a new table, or by using ALTER TABLE or CREATE INDEX on an already existing table.

The following code will create a table, posts, with a full-text index on the content column:

CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  FULLTEXT (`content`)
) ENGINE=InnoDB;

If you already have a table without a full-text index, there are two ways of adding a full-text index to a column. One way is by using ALTER TABLE:

ALTER TABLE `posts` ADD FULLTEXT(`content`);

The other way is by using CREATE INDEX:

CREATE FULLTEXT INDEX `content` ON `posts` (`content`);

If...