Bug #72133 Manual does not provide enough details about InnoDB FTS implementation
Submitted: 26 Mar 2014 8:23 Modified: 3 Jul 2014 18:01
Reporter: Valeriy Kravchuk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: Daniel Price CPU Architecture:Any
Tags: fts, innodb, missing manual

[26 Mar 2014 8:23] Valeriy Kravchuk
Description:
See bug #72132. I wonder where in the manual I can find any explanation for the purpose and features (like tablespace they are stored in) of InnoDB tables like these (created automatically for FULLEXT indexes):

mysql> select name, space from information_schema.innodb_sys_tables where name like 'test/FTS%';
+----------------------------------------------------+-------+
| name                                               | space |
+----------------------------------------------------+-------+
| test/FTS_0000000000000054_000000000000008b_INDEX_1 |     0 |
| test/FTS_0000000000000054_000000000000008b_INDEX_2 |     0 |
| test/FTS_0000000000000054_000000000000008b_INDEX_3 |     0 |
| test/FTS_0000000000000054_000000000000008b_INDEX_4 |     0 |
| test/FTS_0000000000000054_000000000000008b_INDEX_5 |     0 |
| test/FTS_0000000000000054_000000000000008b_INDEX_6 |     0 |
| test/FTS_0000000000000054_BEING_DELETED            |    67 |
| test/FTS_0000000000000054_BEING_DELETED_CACHE      |    68 |
| test/FTS_0000000000000054_CONFIG                   |    69 |
| test/FTS_0000000000000054_DELETED                  |    65 |
| test/FTS_0000000000000054_DELETED_CACHE            |    66 |
+----------------------------------------------------+-------+
22 rows in set (0.64 sec)

Try to find explanation also for relations like these:

mysql> select name, space from information_schema.innodb_sys_tables where table_
id=0x54+0;
+---------+-------+
| name    | space |
+---------+-------+
| test/ft |    64 |
+---------+-------+
1 row in set (0.00 sec)

mysql> select * from information_schema.innodb_sys_indexes where index_id=0x8b+0
\G
*************************** 1. row ***************************
INDEX_ID: 139
    NAME: details
TABLE_ID: 84
    TYPE: 32
N_FIELDS: 1
 PAGE_NO: -1
   SPACE: 64
1 row in set (0.02 sec)

How to repeat:
Use test case from bug #72132 (or just create any InnoDB table with FULLTEXT index), then try to find out in the manual what are all these:

FTS_%_%_INDEX_?

tables are used for and why they are stored in shared tablespace no matter what is your innodb_file_per_table setting is.

Suggested fix:
This article:

http://www.drdobbs.com/database/full-text-search-with-innodb/231902587

gives some hints that must be explained in the manual. Namely, this is how FTS_%_INDEX% tables are really named:

FTS_<table_id_in_hex>_<index_id_in_hex>_INDEX_<partition_number>

So, please, explain all tables used in FTS index implementation, their purpose and features, and also the ways to related them to real table and index.
[26 Mar 2014 10:34] MySQL Verification Team
Hello Valeriy,

Thank you for the report.

Thanks,
Umesh
[3 Jul 2014 18:01] Daniel Price
The FULLTEXT Indexes section of the reference manual has been expanded to provide the requested information about InnoDB FULLTEXT index tables.

http://dev.mysql.com/doc/refman/5.6/en/innodb-fulltext-index.html
http://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

The revised content should appear soon, with the next published documentation build.

Thank you for the bug report.