1

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.

2
  • MyISAM or InnoDB?? – Rick James Jul 5 '18 at 2:35
  • @rickJames innodb – Magnus Jul 5 '18 at 6:59
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.