Documentation Home
MySQL 5.6 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 31.6Mb
PDF (A4) - 31.6Mb
PDF (RPM) - 30.7Mb
HTML Download (TGZ) - 7.6Mb
HTML Download (Zip) - 7.7Mb
HTML Download (RPM) - 6.6Mb
Man Pages (TGZ) - 189.6Kb
Man Pages (Zip) - 303.9Kb
Info (Gzip) - 2.9Mb
Info (Zip) - 2.9Mb
Excerpts from this Manual

MySQL 5.6 Reference Manual  /  ...  /  InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables

14.15.4 InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables

With the introduction of FULLTEXT index support for InnoDB tables in MySQL 5.6.4, the following tables were added to the INFORMATION_SCHEMA database:

mysql> SHOW TABLES FROM INFORMATION_SCHEMA LIKE 'INNODB_FT%';
+-------------------------------------------+
| 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

Note

With the exception of the INNODB_FT_DEFAULT_STOPWORD table, you must set the innodb_ft_aux_table configuration variable to the name of the table (database_name/table_name) that contains the FULLTEXT index. Otherwise, the InnoDB FULLTEXT index INFORMATION_SCHEMA tables appear empty.

Example 14.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.

  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;
    
    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 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 ...');
  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 appear empty, with the exception of the INNODB_FT_DEFAULT_STOPWORD table.

    SET GLOBAL innodb_ft_aux_table = 'test/articles';
  3. Query the INNODB_FT_INDEX_CACHE table, which shows information about newly inserted rows in a FULLTEXT index. To avoid expensive index reorganization during DML operations, data for newly inserted rows remains in the FULLTEXT index cache until OPTIMIZE TABLE is run (or until the server is shutdown 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 innodb_optimize_fulltext_only and run OPTIMIZE TABLE on the table that contains the FULLTEXT index. This operation flushes the contents of the FULLTEXT index cache to the main FULLTEXT index. innodb_optimize_fulltext_only changes the way the OPTIMIZE TABLE statement operates on InnoDB tables, and is intended to be enabled temporarily, during maintenance operations on InnoDB tables with FULLTEXT indexes.

    mysql> SET GLOBAL innodb_optimize_fulltext_only=ON;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> OPTIMIZE TABLE articles;
    +---------------+----------+----------+----------+
    | Table         | Op       | Msg_type | Msg_text |
    +---------------+----------+----------+----------+
    | test.articles | optimize | status   | OK       |
    +---------------+----------+----------+----------+
  5. Query the INNODB_FT_INDEX_TABLE table to view information about data in the main FULLTEXT index, including information about the data that was just flushed from the FULLTEXT 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 |
    +------------+--------------+-------------+-----------+--------+----------+

    The INNODB_FT_INDEX_CACHE table is now empty since the OPTIMIZE TABLE operation flushed the FULLTEXT index cache.

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE LIMIT 5;
    Empty set (0.00 sec)
  6. Delete some records from the test/articles table.

    mysql> DELETE FROM test.articles WHERE id < 4;
    Query OK, 3 rows affected (0.11 sec)
  7. Query the INNODB_FT_DELETED table. This table records rows that are deleted from the FULLTEXT 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 run OPTIMIZE TABLE.

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
    +--------+
    | DOC_ID |
    +--------+
    |      2 |
    |      3 |
    |      4 |
    +--------+
  8. Run OPTIMIZE TABLE to remove the deleted records.

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

    The INNODB_FT_DELETED table should now appear empty.

    mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FT_DELETED;
    Empty set (0.00 sec)
  9. Query the INNODB_FT_CONFIG table. This table contains metadata about the FULLTEXT index and related processing:

    • optimize_checkpoint_limit is the number of seconds after which an OPTIMIZE TABLE run stops.

    • synced_doc_id is the next DOC_ID to be issued.

    • stopword_table_name is the database/table name for a user-defined stopword table. This field appears empty if there is no user-defined stopword table.

    • use_stopword indicates whether or not a stopword table is used, which is defined when the FULLTEXT 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     |
    +---------------------------+-------+


User Comments
Sign Up Login You must be logged in to post a comment.