Movatterモバイル変換

17.15.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables

1. Create a table with aFULLTEXT 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 theinnodb_ft_aux_table variable to the name of the table with theFULLTEXT index. If this variable is not set, theInnoDBFULLTEXTINFORMATION_SCHEMA tables are empty, with the exception ofINNODB_FT_DEFAULT_STOPWORD.

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

3. Query theINNODB_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 |+------------+--------------+-------------+-----------+--------+----------+

4. Enable theinnodb_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       |+---------------+----------+----------+----------+

5. Query theINNODB_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 |+------------+--------------+-------------+-----------+--------+----------+

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

6. Delete some records from thetest/articles table.

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

7. Query theINNODB_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 |+--------+

8. RunOPTIMIZE TABLE to remove the deleted records.

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

   TheINNODB_FT_DELETED table should now be empty.

   mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;Empty set (0.00 sec)

9. Query theINNODB_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     |+---------------------------+-------+

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

    mysql> SET GLOBAL innodb_optimize_fulltext_only=OFF;