FULLTEXT インデックスを含むテーブルを作成し、一部のデータを挿入します。

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

innodb_ft_aux_table 変数を FULLTEXT インデックスを含むテーブルの名前に設定します。 この変数が設定されていない場合、INNODB_FT_DEFAULT_STOPWORD を除き、InnoDB FULLTEXT INFORMATION_SCHEMA テーブルは空です。

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

INNODB_FT_INDEX_CACHE テーブルをクエリーします。これにより、FULLTEXT インデックス内の新しく挿入された行に関する情報が示されます。 DML 操作中の高コストのインデックス再編成を回避するために、新しく挿入された行のデータは、OPTIMIZE TABLE が実行されるまで (またはサーバーが停止するか、キャッシュ制限を超えるまで)、FULLTEXT インデックスキャッシュに残ります。

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 |
+------------+--------------+-------------+-----------+--------+----------+

innodb_optimize_fulltext_only システム変数を有効にし、FULLTEXT インデックスを含むテーブルで OPTIMIZE TABLE を実行します。 この操作により、FULLTEXT インデックスキャッシュの内容がメインの FULLTEXT インデックスにフラッシュされます。innodb_optimize_fulltext_only は、InnoDB テーブルでの OPTIMIZE TABLE ステートメントの動作方法を変更するものであり、FULLTEXT インデックスを含む InnoDB テーブルでの保守操作中に一時的に有効にすることを目的にしています。

mysql> SET GLOBAL innodb_optimize_fulltext_only=ON;

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

INNODB_FT_INDEX_TABLE テーブルにクエリーして、メインの FULLTEXT インデックス内のデータに関する情報 (FULLTEXT インデックスキャッシュからフラッシュされたばかりのデータに関する情報を含む) を表示します。

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 |
+------------+--------------+-------------+-----------+--------+----------+

OPTIMIZE TABLE 操作によって FULLTEXT インデックスキャッシュがフラッシュされたため、INNODB_FT_INDEX_CACHE テーブルは空になっています。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE LIMIT 5;
Empty set (0.00 sec)

test/articles テーブルからいくつかのレコードを削除します。

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

INNODB_FT_DELETED テーブルをクエリーします。 このテーブルには、FULLTEXT インデックスから削除された行が記録されます。 DML 操作中にコストの高いインデックス再編成が行われないようにするために、新しく削除されたレコードに関する情報は個別に格納され、テキスト検索を実行すると検索結果からフィルタで除外され、OPTIMIZE TABLE を実行するとメインの検索インデックスから削除されます。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
+--------+
| DOC_ID |
+--------+
|      2 |
|      3 |
|      4 |
+--------+

OPTIMIZE TABLE を実行して、削除されたレコードを消去します。

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

これで、INNODB_FT_DELETED テーブルは空になります。

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

INNODB_FT_CONFIG テーブルをクエリーします。 このテーブルには、FULLTEXT インデックスに関するメタデータとそれに関連する処理が含まれています。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_CONFIG;
+---------------------------+-------+
| KEY                       | VALUE |
+---------------------------+-------+
| optimize_checkpoint_limit | 180   |
| synced_doc_id             | 8     |
| stopword_table_name       |       |
| use_stopword              | 1     |
+---------------------------+-------+

innodb_optimize_fulltext_only は一時的にのみ有効にすることを意図しているため、無効にします:

mysql> SET GLOBAL innodb_optimize_fulltext_only=OFF;