Full-Text Search in InnoDB

I used to keep a MyISAM table around just to get MATCH ... AGAINST, and I paid for it with crash recovery and locking headaches. InnoDB has had full-text indexes since 5.6, and by 5.7 and 8.0 it's solid enough that I default to it for product catalogs, knowledge bases and log search where a dedicated search engine would be overkill.

I'll add a full-text index to an existing table, go through the search modes, tune what gets indexed, and cover what InnoDB does differently from MyISAM. Examples are on MySQL 8.0 and work on 5.7. MariaDB 10.x supports the same syntax.

Adding the index

ALTER TABLE articles
  ADD FULLTEXT INDEX ft_title_body (title, body);

On a large table this is an in-place operation but requires LOCK=SHARED, so writes block while the index builds. Because the first full-text index on a table also adds a hidden FTS_DOC_ID column and rebuilds the table, it is worth creating an explicit one in the schema if you expect to add or drop full-text indexes more than once:

ALTER TABLE articles ADD COLUMN FTS_DOC_ID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE;

The column must have exactly that name and type. With it present, subsequent full-text index additions skip the table rebuild.

Columns in a single MATCH() must all belong to one full-text index. If you want to search title alone as well as title, body together, you need two indexes.

Natural language mode

The default mode ranks rows by relevance:

SELECT id, title,
       MATCH(title, body) AGAINST ('replication lag') AS score
FROM articles
WHERE MATCH(title, body) AGAINST ('replication lag')
ORDER BY score DESC
LIMIT 20;

The optimizer recognises the identical MATCH() in the select list and the WHERE and evaluates it once. Scores are floats; larger means more relevant, and rows that match nothing are excluded by the WHERE.

Boolean mode

Boolean mode gives you operators and skips relevance ranking unless you sort explicitly:

SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST ('+innodb -myisam "buffer pool"' IN BOOLEAN MODE);

• +word must be present, -word must be absent
• "phrase here" matches the exact sequence
• word* matches a prefix (repl* finds replication and replica)
• >word and `