The following tables provide metadata for
FULLTEXT
indexes:
mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_FT%';
+-------------------------------------------+
| Tables_in_INFORMATION_SCHEMA (INNODB_FT%) |
+-------------------------------------------+
| INNODB_FT_CONFIG |
| INNODB_FT_BEING_DELETED |
| INNODB_FT_DELETED |
| INNODB_FT_DEFAULT_STOPWORD |
| INNODB_FT_INDEX_TABLE |
| INNODB_FT_INDEX_CACHE |
+-------------------------------------------+
Table Overview
INNODB_FT_CONFIG
: Provides metadata about theFULLTEXT
index and associated processing for anInnoDB
table.INNODB_FT_BEING_DELETED
: Provides a snapshot of theINNODB_FT_DELETED
table; it is used only during anOPTIMIZE TABLE
maintenance operation. WhenOPTIMIZE TABLE
is run, theINNODB_FT_BEING_DELETED
table is emptied, andDOC_ID
values are removed from theINNODB_FT_DELETED
table. Because the contents ofINNODB_FT_BEING_DELETED
typically have a short lifetime, this table has limited utility for monitoring or debugging. For information about runningOPTIMIZE TABLE
on tables withFULLTEXT
indexes, see Section 14.9.6, “Fine-Tuning MySQL Full-Text Search”.INNODB_FT_DELETED
: Stores rows that are deleted from theFULLTEXT
index for anInnoDB
table. To avoid expensive index reorganization during DML operations for anInnoDB
FULLTEXT
index, the information about newly deleted words is stored separately, filtered out of search results when you do a text search, and removed from the main search index only when you issue anOPTIMIZE TABLE
statement for theInnoDB
table.INNODB_FT_DEFAULT_STOPWORD
: Holds a list of stopwords that are used by default when creating aFULLTEXT
index onInnoDB
tables.For information about the
INNODB_FT_DEFAULT_STOPWORD
table, see Section 14.9.4, “Full-Text Stopwords”.INNODB_FT_INDEX_TABLE
: Provides information about the inverted index used to process text searches against theFULLTEXT
index of anInnoDB
table.INNODB_FT_INDEX_CACHE
: Provides token information about newly inserted rows in aFULLTEXT
index. To avoid expensive index reorganization during DML operations, the information about newly indexed words is stored separately, and combined with the main search index only whenOPTIMIZE TABLE
is run, when the server is shut down, or when the cache size exceeds a limit defined by theinnodb_ft_cache_size
orinnodb_ft_total_cache_size
system variable.
With the exception of the
INNODB_FT_DEFAULT_STOPWORD
table,
these tables are empty initially. Before querying any of them,
set the value of the
innodb_ft_aux_table
system
variable to the name (including the database name) of the table
that contains the FULLTEXT
index (for
example, test/articles
).
Example 17.5 InnoDB FULLTEXT Index INFORMATION_SCHEMA Tables
This example uses a table with a FULLTEXT
index to demonstrate the data contained in the
FULLTEXT
index
INFORMATION_SCHEMA
tables.
Create a table with a
FULLTEXT
index 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 the
innodb_ft_aux_table
variable to the name of the table with theFULLTEXT
index. If this variable is not set, theInnoDB
FULLTEXT
INFORMATION_SCHEMA
tables are empty, with the exception ofINNODB_FT_DEFAULT_STOPWORD
.mysql> SET GLOBAL innodb_ft_aux_table = 'test/articles';
Query the
INNODB_FT_INDEX_CACHE
table, which shows information about newly inserted rows in aFULLTEXT
index. To avoid expensive index reorganization during DML operations, data for newly inserted rows remains in theFULLTEXT
index cache untilOPTIMIZE TABLE
is run (or until the server is shut down or cache limits are exceeded).mysql> SELECT * FROM INFORMATION_SCHEMA.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 the
innodb_optimize_fulltext_only
system variable and runOPTIMIZE TABLE
on the table that contains theFULLTEXT
index. This operation flushes the contents of theFULLTEXT
index cache to the mainFULLTEXT
index.innodb_optimize_fulltext_only
changes the way theOPTIMIZE TABLE
statement operates onInnoDB
tables, and is intended to be enabled temporarily, during maintenance operations onInnoDB
tables withFULLTEXT
indexes.mysql> SET GLOBAL innodb_optimize_fulltext_only=ON; mysql> OPTIMIZE TABLE articles; +---------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+----------+----------+----------+ | test.articles | optimize | status | OK | +---------------+----------+----------+----------+
Query the
INNODB_FT_INDEX_TABLE
table to view information about data in the mainFULLTEXT
index, including information about the data that was just flushed from theFULLTEXT
index cache.mysql> SELECT * FROM INFORMATION_SCHEMA.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 | +------------+--------------+-------------+-----------+--------+----------+
The
INNODB_FT_INDEX_CACHE
table is now empty since theOPTIMIZE TABLE
operation flushed theFULLTEXT
index cache.mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE LIMIT 5; Empty set (0.00 sec)
Delete some records from the
test/articles
table.mysql> DELETE FROM test.articles WHERE id < 4;
Query the
INNODB_FT_DELETED
table. This table records rows that are deleted from theFULLTEXT
index. 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
.mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; +--------+ | DOC_ID | +--------+ | 2 | | 3 | | 4 | +--------+
Run
OPTIMIZE TABLE
to remove the deleted records.mysql> OPTIMIZE TABLE articles; +---------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+----------+----------+----------+ | test.articles | optimize | status | OK | +---------------+----------+----------+----------+
The
INNODB_FT_DELETED
table should now be empty.mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; Empty set (0.00 sec)
Query the
INNODB_FT_CONFIG
table. This table contains metadata about theFULLTEXT
index and related processing:optimize_checkpoint_limit
: The number of seconds after which anOPTIMIZE TABLE
run stops.synced_doc_id
: The nextDOC_ID
to be issued.stopword_table_name
: Thedatabase/table
name for a user-defined stopword table. TheVALUE
column is empty if there is no user-defined stopword table.use_stopword
: Indicates whether a stopword table is used, which is defined when theFULLTEXT
index is created.
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG; +---------------------------+-------+ | KEY | VALUE | +---------------------------+-------+ | optimize_checkpoint_limit | 180 | | synced_doc_id | 8 | | stopword_table_name | | | use_stopword | 1 | +---------------------------+-------+
Disable
innodb_optimize_fulltext_only
, since it is intended to be enabled only temporarily:mysql> SET GLOBAL innodb_optimize_fulltext_only=OFF;