Rechercher dans le manuel MySQL
15.14.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables
The following tables provide metadata for
FULLTEXT
indexes:
- +-------------------------------------------+
- | 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 12.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 12.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 15.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:- body TEXT,
- ('MySQL Tutorial','DBMS stands for DataBase ...'),
- ('How To Use MySQL Well','After you went through a ...'),
- ('Optimizing MySQL','In this tutorial we will 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
.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).- +------------+--------------+-------------+-----------+--------+----------+
- +------------+--------------+-------------+-----------+--------+----------+
- | 1001 | 5 | 5 | 1 | 5 | 0 |
- | comparison | 6 | 6 | 1 | 6 | 44 |
- | configured | 7 | 7 | 1 | 7 | 20 |
- +------------+--------------+-------------+-----------+--------+----------+
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.- +---------------+----------+----------+----------+
- +---------------+----------+----------+----------+
- +---------------+----------+----------+----------+
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.- +------------+--------------+-------------+-----------+--------+----------+
- +------------+--------------+-------------+-----------+--------+----------+
- | 1001 | 5 | 5 | 1 | 5 | 0 |
- | comparison | 6 | 6 | 1 | 6 | 44 |
- | configured | 7 | 7 | 1 | 7 | 20 |
- +------------+--------------+-------------+-----------+--------+----------+
The
INNODB_FT_INDEX_CACHE
table is now empty since theOPTIMIZE TABLE
operation flushed theFULLTEXT
index cache.Delete some records from the
test/articles
table.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
.Run
OPTIMIZE TABLE
to remove the deleted records.- +---------------+----------+----------+----------+
- +---------------+----------+----------+----------+
- +---------------+----------+----------+----------+
The
INNODB_FT_DELETED
table should now be empty.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.
Disable
innodb_optimize_fulltext_only
, since it is intended to be enabled only temporarily:
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-innodb-information-schema-fulltext-index-tables.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.