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:
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-innodb-information-schema-fulltext-index-tables.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.