How to measure the size of InnoDB's fulltext index

Question

How can I determine how much space MySQLs fulltext index is taking up, using innodb?

Solution would ideally be by querying an information_schema table rather than looking at the filesystem.

Answer 1

I don't know the answer; let me try to figure it out. First, I don't see anything likely in information_schema or mysql. In the filesystem, in the directory with same name as the database, look for files starting with FTS_. If you have multiple tables with FULLTEXT indexes, segregate them by prefix, then sum up the sizes. For one table, I see

SHOW CREATE TABLE fti

CREATE TABLE `fti` (
  `msg_id` mediumint(8) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `msg` longtext NOT NULL,
  PRIMARY KEY (`msg_id`),
  FULLTEXT KEY `title_msg` (`title`,`msg`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `msg` (`msg`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

mysql> SHOW TABLE STATUS LIKE 'fti'\G

           Name: fti
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 175483
 Avg_row_length: 1766
    Data_length: 310050816
Max_data_length: 0
   Index_length: 3686400
      Data_free: 4194304

ls -l FTS* (blank lines added)

-rw-rw---- 1 mysql mysql 37748736 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_1.ibd
-rw-rw---- 1 mysql mysql 83886080 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_2.ibd
-rw-rw---- 1 mysql mysql 50331648 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_3.ibd
-rw-rw---- 1 mysql mysql 50331648 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_4.ibd
-rw-rw---- 1 mysql mysql 83886080 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_5.ibd
-rw-rw---- 1 mysql mysql 30408704 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_6.ibd

-rw-rw---- 1 mysql mysql   507904 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_1.ibd
-rw-rw---- 1 mysql mysql 13631488 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_2.ibd
-rw-rw---- 1 mysql mysql 11534336 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_3.ibd
-rw-rw---- 1 mysql mysql 11534336 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_4.ibd
-rw-rw---- 1 mysql mysql 14680064 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_5.ibd
-rw-rw---- 1 mysql mysql 10485760 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_6.ibd

-rw-rw---- 1 mysql mysql 37748736 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_1.ibd
-rw-rw---- 1 mysql mysql 79691776 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_2.ibd
-rw-rw---- 1 mysql mysql 50331648 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_3.ibd
-rw-rw---- 1 mysql mysql 50331648 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_4.ibd
-rw-rw---- 1 mysql mysql 83886080 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_5.ibd
-rw-rw---- 1 mysql mysql 29360128 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_6.ibd

-rw-rw---- 1 mysql mysql    98304 Jan  9  2017 FTS_0000000000000090_BEING_DELETED_CACHE.ibd
-rw-rw---- 1 mysql mysql    98304 Jan  9  2017 FTS_0000000000000090_BEING_DELETED.ibd
-rw-rw---- 1 mysql mysql    98304 Jan 16  2017 FTS_0000000000000090_CONFIG.ibd
-rw-rw---- 1 mysql mysql    98304 Jan  9  2017 FTS_0000000000000090_DELETED_CACHE.ibd
-rw-rw---- 1 mysql mysql    98304 Jan  9  2017 FTS_0000000000000090_DELETED.ibd

The files add up to about 340MB for the largest index (title+msg).

The total text size (SUM(LENGTH...) is 220MB; 310MB is about right once you add on the off-record storage overhead. It seems reasonable for the FT index for a text column to be about the same bulk as the text.