content_image/672783.jpg
OPTIMIZE TABLE  on tables with FULLTEXT  indexes, see Section 12.10.6, “Fine-Tuning MySQL Full- Text Search” . 

• INNODB_FT_DELETED : Stores rows that are deleted from the FULLTEXT  index for an InnoDB  table. To avoid expensive index reorganization during DML operations for an InnoDB 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 an OPTIMIZE TABLE statement for the InnoDB  table. 

• INNODB_FT_DEFAULT_STOPWORD : Holds a list of stopwords that are used by default when creating a FULLTEXT  index on InnoDB  tables. 

For information about the INNODB_FT_DEFAULT_STOPWORD  table, see Section 12.10.4, “Full-Text Stopwords” . 

• INNODB_FT_INDEX_TABLE : Provides information about the inverted index used to process text searches against the FULLTEXT  index of an InnoDB  table. 

• INNODB_FT_INDEX_CACHE : Provides token information about newly inserted rows in a FULLTEXT 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 when OPTIMIZE TABLE  is run, when the server is shut down, or when the cache size exceeds a limit defined by the innodb_ft_cache_size  or innodb_ft_total_cache_size  system variable. 

<img src='content_image/672783.jpg'>

## Example 15.5 InnoDB FULLTEXT Index INFORMATION_SCHEMA Tables 

## Note 

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 ). 

This example uses a table with a FULLTEXT  index to demonstrate the data contained in the FULLTEXT index INFORMATION_SCHEMA  tables. 

1. 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 ...’); 

2. Set the innodb_ft_aux_table  variable to the name of the table with the FULLTEXT  index. If this variable is not set, the InnoDB FULLTEXT INFORMATION_SCHEMA  tables are empty, with the exception of INNODB_FT_DEFAULT_STOPWORD . 

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

