full text html,MySQL :: MySQL 5.6 Reference Manual :: 14.6.2.3 InnoDB Full-Text Indexes

InnoDB Full-Text Index Tables

When an InnoDB full-text index is created, a

set of index tables is created, as shown in the following

example:

The first six index tables comprise the inverted index and are

referred to as auxiliary index tables. When incoming documents

are tokenized, the individual words (also referred to as

“tokens”) are inserted into the index tables along

with position information and an associated

DOC_ID. The words are fully sorted and

partitioned among the six index tables based on the character

set sort weight of the word's first character.

The inverted index is partitioned into six auxiliary index

tables to support parallel index creation. By default, two

threads tokenize, sort, and insert words and associated data

into the index tables. The number of threads that perform this

work is configurable using the

innodb_ft_sort_pll_degree

variable. Consider increasing the number of threads when

creating full-text indexes on large tables.

Auxiliary index table names are prefixed with

fts_ and postfixed with

index_#. Each

auxiliary index table is associated with the indexed table by a

hex value in the auxiliary index table name that matches the

table_id of the indexed table. For example,

the table_id of the

test/opening_lines table is

327, for which the hex value is 0x147. As

shown in the preceding example, the “147” hex value

appears in the names of auxiliary index tables that are

associated with the test/opening_lines table.

A hex value representing the index_id of the

full-text index also appears in auxiliary index table names. For

example, in the auxiliary table name

test/FTS_0000000000000147_00000000000001c9_INDEX_1,

the hex value 1c9 has a decimal value of 457.

The index defined on the opening_lines table

(idx) can be identified by querying the

INFORMATION_SCHEMA.INNODB_SYS_INDEXES

table for this value (457).

Index tables are stored in their own tablespace when

innodb_file_per_table is

enabled. If

innodb_file_per_table is

disabled, index tables are stored in the

InnoDB system tablespace (space 0).

Note

Due to a bug introduced in MySQL 5.6.5, index tables are

created in the InnoDB system tablespace

(space 0) when

innodb_file_per_table is

enabled. The bug is fixed in MySQL 5.6.20 and MySQL 5.7.5

(Bug#18635485).

The other index tables shown in the preceding example are

referred to as common index tables and are used for deletion

handling and storing the internal state of full-text indexes.

Unlike the inverted index tables, which are created for each

full-text index, this set of tables is common to all full-text

indexes created on a particular table.

Common index tables are retained even if full-text indexes are

dropped. When a full-text index is dropped, the

FTS_DOC_ID column that was created for the

index is retained, as removing the FTS_DOC_ID

column would require rebuilding the previously indexed table.

Common index tables are required to manage the

FTS_DOC_ID column.

FTS_*_DELETED and

FTS_*_DELETED_CACHE

Contain the document IDs (DOC_ID) for documents that are

deleted but whose data is not yet removed from the full-text

index. The FTS_*_DELETED_CACHE is the

in-memory version of the FTS_*_DELETED

table.

FTS_*_BEING_DELETED and

FTS_*_BEING_DELETED_CACHE

Contain the document IDs (DOC_ID) for documents that are

deleted and whose data is currently in the process of being

removed from the full-text index. The

FTS_*_BEING_DELETED_CACHE table is the

in-memory version of the

FTS_*_BEING_DELETED table.

FTS_*_CONFIG

Stores information about the internal state of the full-text

index. Most importantly, it stores the

FTS_SYNCED_DOC_ID, which identifies

documents that have been parsed and flushed to disk. In case

of crash recovery, FTS_SYNCED_DOC_ID

values are used to identify documents that have not been

flushed to disk so that the documents can be re-parsed and

added back to the full-text index cache. To view the data in

this table, query the

INFORMATION_SCHEMA.INNODB_FT_CONFIG

table.