Create a table with aFULLTEXTindex and insert some data:

mysql> CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT (title,body) ) ENGINE=InnoDB; mysql> INSERT INTO articles (title,body) VALUES ('MySQL Tutorial','DBMS stands for DataBase ...'), ('How To Use MySQL Well','After you went through a ...'), ('Optimizing MySQL','In this tutorial we show ...'), ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), ('MySQL vs. YourSQL','In the following database comparison ...'), ('MySQL Security','When configured properly, MySQL ...');

Set theinnodb_ft_aux_tablevariable to the name of the table with theFULLTEXTindex. If this variable is not set, theInnoDBFULLTEXTINFORMATION_SCHEMAtables are empty, with the exception ofINNODB_FT_DEFAULT_STOPWORD.

mysql> SET GLOBAL innodb_ft_aux_table = 'test/articles';

Query theINNODB_FT_INDEX_CACHEtable, which shows information about newly inserted rows in aFULLTEXTindex. To avoid expensive index reorganization during DML operations, data for newly inserted rows remains in theFULLTEXTindex cache untilOPTIMIZE TABLEis run (or until the server is shut down or cache limits are exceeded).

从INFORMATION_SCHEMA mysql > SELECT *。INNODB_FT_INDEX_CACHE LIMIT 5; +------------+--------------+-------------+-----------+--------+----------+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +------------+--------------+-------------+-----------+--------+----------+ | 1001 | 5 | 5 | 1 | 5 | 0 | | after | 3 | 3 | 1 | 3 | 22 | | comparison | 6 | 6 | 1 | 6 | 44 | | configured | 7 | 7 | 1 | 7 | 20 | | database | 2 | 6 | 2 | 2 | 31 | +------------+--------------+-------------+-----------+--------+----------+

Enable theinnodb_optimize_fulltext_onlysystem variable and runOPTIMIZE TABLEon the table that contains theFULLTEXTindex. This operation flushes the contents of theFULLTEXTindex cache to the mainFULLTEXTindex.innodb_optimize_fulltext_onlychanges the way theOPTIMIZE TABLEstatement operates onInnoDBtables, and is intended to be enabled temporarily, during maintenance operations onInnoDBtables withFULLTEXTindexes.

mysql> SET GLOBAL innodb_optimize_fulltext_only=ON; mysql> OPTIMIZE TABLE articles; +---------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+----------+----------+----------+ | test.articles | optimize | status | OK | +---------------+----------+----------+----------+

Query theINNODB_FT_INDEX_TABLEtable to view information about data in the mainFULLTEXTindex, including information about the data that was just flushed from theFULLTEXTindex cache.

从INFORMATION_SCHEMA mysql > SELECT *。INNODB_FT_INDEX_TABLE LIMIT 5; +------------+--------------+-------------+-----------+--------+----------+ | WORD | FIRST_DOC_ID | LAST_DOC_ID | DOC_COUNT | DOC_ID | POSITION | +------------+--------------+-------------+-----------+--------+----------+ | 1001 | 5 | 5 | 1 | 5 | 0 | | after | 3 | 3 | 1 | 3 | 22 | | comparison | 6 | 6 | 1 | 6 | 44 | | configured | 7 | 7 | 1 | 7 | 20 | | database | 2 | 6 | 2 | 2 | 31 | +------------+--------------+-------------+-----------+--------+----------+

TheINNODB_FT_INDEX_CACHEtable is now empty since theOPTIMIZE TABLEoperation flushed theFULLTEXTindex cache.

从INFORMATION_SCHEMA mysql > SELECT *。INNODB_FT_INDEX_CACHE LIMIT 5; Empty set (0.00 sec)

Delete some records from thetest/articlestable.

mysql> DELETE FROM test.articles WHERE id < 4;

Query theINNODB_FT_DELETEDtable. This table records rows that are deleted from theFULLTEXTindex. To avoid expensive index reorganization during DML operations, information about newly deleted records is stored separately, filtered out of search results when you do a text search, and removed from the main search index when you runOPTIMIZE TABLE.

从INFORMATION_SCHEMA mysql > SELECT *。INNODB_FT_DELETED; +--------+ | DOC_ID | +--------+ | 2 | | 3 | | 4 | +--------+

RunOPTIMIZE TABLEto remove the deleted records.

mysql> OPTIMIZE TABLE articles; +---------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+----------+----------+----------+ | test.articles | optimize | status | OK | +---------------+----------+----------+----------+

TheINNODB_FT_DELETEDtable should now be empty.

从INFORMATION_SCHEMA mysql > SELECT *。INNODB_FT_DELETED; Empty set (0.00 sec)

Query theINNODB_FT_CONFIGtable. This table contains metadata about theFULLTEXTindex and related processing:

从INFORMATION_SCHEMA mysql > SELECT *。INNODB_FT_CONFIG; +---------------------------+-------+ | KEY | VALUE | +---------------------------+-------+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 8 | | stopword_table_name | | | use_stopword | 1 | +---------------------------+-------+

Disableinnodb_optimize_fulltext_only, since it is intended to be enabled only temporarily:

mysql> SET GLOBAL innodb_optimize_fulltext_only=OFF;