Understanding MySQL Full-Text Indexes: Structure, Auxiliary Tables, Buffer Pool, and Transaction Handling

After introducing B‑tree and hash indexes, this article examines MySQL full-text indexes, which solve the performance problems of fuzzy LIKE queries by creating an inverted index that stores each word (or token) together with the document IDs and positions where it appears.

The full-text index is built on a table such as:

CREATE TABLE ft_sample (
    id INT PRIMARY KEY,
    s1 VARCHAR(200),
    log_time DATETIME,
    s2 TEXT,
    KEY idx_log_time (log_time)
);

Adding a full-text index on s1 creates several auxiliary InnoDB tables (named ytt/fts_…_index_1 to _index_6 ) that store the actual inverted‑list data, a configuration table, and tables that track deleted or being‑deleted document IDs. These auxiliary tables allow parallel processing of tokenization (controlled by innodb_ft_sort_pll_degree ) and avoid hotspot contention on the main index.

The buffer pool information_schema.innodb_ft_index_cache temporarily holds insert/update operations for full-text columns; when the cache reaches the size defined by innodb_ft_cache_size (default 8 MB) it flushes the data to the auxiliary index tables. The total instance cache size is controlled by innodb_ft_total_cache_size (default 640 MB).

Each indexed term is associated with a DOC_ID (stored as FTS_DOC_ID ), which uniquely identifies a row in the full-text index. If the column is not explicitly defined, MySQL creates a hidden FTS_DOC_ID field that persists even after the index is dropped.

Full-text indexes have special transaction semantics: changes made within a transaction are not visible to MATCH…AGAINST queries until the transaction is committed. The article demonstrates this with a series of INSERT statements followed by a SELECT before and after COMMIT .

Overall, the piece provides a comprehensive view of MySQL full-text index structures, auxiliary tables, caching, document IDs, and transaction behavior, preparing readers for the next article on effective full-text index usage.