The permitted gap between the largest used FTS_DOC_ID  value and new FTS_DOC_ID  value is 65535. 

To avoid rebuilding the table, the FTS_DOC_ID  column is retained when dropping a full-text index. 

## InnoDB Full-Text Index Deletion Handling 

Deleting a record that has a full-text index column could result in numerous small deletions in the auxiliary index tables, making concurrent access to these tables a point of contention. To avoid this problem, the DOC_ID  of a deleted document is logged in a special FTS_*_DELETED  table whenever a record is deleted from an indexed table, and the indexed record remains in the full-text index. Before returning query results, information in the FTS_*_DELETED  table is used to filter out deleted DOC_ID s. The benefit of this design is that deletions are fast and inexpensive. The drawback is that the size of the index is not immediately reduced after deleting records. To remove full-text index entries for deleted records, run OPTIMIZE TABLE on the indexed table with innodb_optimize_fulltext_only=ON  to rebuild the full-text index. For more information, see Optimizing InnoDB Full-Text Indexes. 

## InnoDB Full-Text Index Transaction Handling 

InnoDB  full-text indexes have special transaction handling characteristics due its caching and batch processing behavior. Specifically, updates and insertions on a full-text index are processed at transaction commit time, which means that a full-text search can only see committed data. The following example demonstrates this behavior. The full-text search only returns a result after the inserted lines are committed. 

mysql> CREATE TABLE opening_lines ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, opening_line TEXT(500), author VARCHAR(200), title VARCHAR(200), FULLTEXT idx (opening_line) ) ENGINE=InnoDB; mysql> BEGIN; mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES (’Call me Ishmael.’,’Herman Melville’,’Moby-Dick’), (’A screaming comes across the sky.’,’Thomas Pynchon’,’Gravity\’s Rainbow’), (’I am an invisible man.’,’Ralph Ellison’,’Invisible Man’), (’Where now? Who now? When now?’,’Samuel Beckett’,’The Unnamable’), (’It was love at first sight.’,’Joseph Heller’,’Catch-22’), (’All this happened, more or less.’,’Kurt Vonnegut’,’Slaughterhouse-Five’), (’Mrs. Dalloway said she would buy the flowers herself.’,’Virginia Woolf’,’Mrs. Dalloway’), (’It was a pleasure to burn.’,’Ray Bradbury’,’Fahrenheit 451’); mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST(’Ishmael’); +----------+ | COUNT(*) | +----------+ | 0 | +----------+ mysql> COMMIT; mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST(’Ishmael’); +----------+ | COUNT(*) | +----------+ | 1 | +----------+ 

## Monitoring InnoDB Full-Text Indexes 

You can monitor and examine the special text-processing aspects of InnoDB  full-text indexes by querying the following INFORMATION_SCHEMA  tables: 

