Indexes • fts_*_deleted  and  fts_*_deleted_cache • fts_*_being_deleted  and  fts_*_being_deleted_cache • fts_*_config InnoDB Full-Text Index Cache The full-text index cache stores the same information as auxiliary index tables. However, the full-text index cache only caches tokenized data for recently inserted rows. The data that is already flushed to disk (to the auxiliary index tables) is not brought back into the full-text index cache when queried. The data in auxiliary index tables is queried directly, and results from the auxiliary index tables are merged with results from the full-text index cache before being returned. The innodb_ft_cache_size  variable is used to configure the full-text index cache size (on a per- table basis), which affects how often the full-text index cache is flushed. You can also define a global full- text index cache size limit for all tables in a given instance using the  innodb_ft_total_cache_size variable. The caching and batch flushing behavior avoids frequent updates to auxiliary index tables, which could result in concurrent access issues during busy insert and update times. The batching technique also avoids multiple insertions for the same word, and minimizes duplicate entries. Instead of flushing each word individually, insertions for the same word are merged and flushed to disk as a single entry, improving insertion efficiency while keeping auxiliary index tables as small as possible. When a document is inserted, it is tokenized, and the individual words and associated data are inserted into the full-text index. This process, even for small documents, can result in numerous small insertions into the auxiliary index tables, making concurrent access to these tables a point of contention. To avoid this problem,  InnoDB  uses a full-text index cache to temporarily cache index table insertions for recently inserted rows. This in-memory cache structure holds insertions until the cache is full and then batch flushes them to disk (to the auxiliary index tables). You can query the INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE  table to view tokenized data for recently inserted rows. InnoDB  uses a unique document identifier referred to as the  DOC_ID  to map words in the full-text index to document records where the word appears. The mapping requires an  FTS_DOC_ID  column on the indexed table. If an  FTS_DOC_ID  column is not defined,  InnoDB  automatically adds a hidden  FTS_DOC_ID  column when the full-text index is created. The following example demonstrates this behavior. The following table definition does not include an  FTS_DOC_ID  column: 3046 InnoDB Full-Text Index DOC_ID and FTS_DOC_ID Column 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. 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. 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.