SET GLOBAL innodb_optimize_fulltext_only=ON; CREATE TABLE book ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, heading VARCHAR(20), content TEXT, FULLTEXT idx (content) ) ENGINE= InnoDB; CREATE PROCEDURE find (word CHAR(20)) SELECT heading FROM book WHERE MATCH(content) AGAINST (word IN NATURAL LANGUAGE MODE) ORDER BY id; LOAD DATA INFILE '/std_data/lob/frankenstein.txt' INTO TABLE book FIELDS TERMINATED BY '#' LINES TERMINATED BY '@' (heading, content); CALL find('they'); heading Letter 1 Letter 2 Letter 4 Chapter 1 Chapter 2 Chapter 3 Chapter 5 Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10 Chapter 11 Chapter 12 Chapter 13 Chapter 14 Chapter 15 Chapter 16 Chapter 17 Chapter 18 Chapter 19 Chapter 20 Chapter 21 Chapter 22 Chapter 23 Chapter 24 CALL find('wretched'); heading Letter 4 Chapter 5 Chapter 7 Chapter 8 Chapter 9 Chapter 10 Chapter 11 Chapter 12 Chapter 15 Chapter 20 Chapter 21 Chapter 24 CALL find('mariner'); heading Letter 2 Chapter 5 SET GLOBAL innodb_ft_aux_table="test/book"; OPTIMIZE TABLE book; Table Op Msg_type Msg_text test.book optimize status OK CALL find('they'); heading Letter 1 Letter 2 Letter 4 Chapter 1 Chapter 2 Chapter 3 Chapter 5 Chapter 6 Chapter 7 Chapter 8 Chapter 9 Chapter 10 Chapter 11 Chapter 12 Chapter 13 Chapter 14 Chapter 15 Chapter 16 Chapter 17 Chapter 18 Chapter 19 Chapter 20 Chapter 21 Chapter 22 Chapter 23 Chapter 24 CALL find('wretched'); heading Letter 4 Chapter 5 Chapter 7 Chapter 8 Chapter 9 Chapter 10 Chapter 11 Chapter 12 Chapter 15 Chapter 20 Chapter 21 Chapter 24 CALL find('mariner'); heading Letter 2 Chapter 5 SET GLOBAL innodb_ft_aux_table="test/book"; SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; count(*) 47753 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; count(*) 0 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; count(*) 0 DELETE FROM book; CALL find('they'); heading SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; count(*) 28 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; count(*) 47753 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; count(*) 0 OPTIMIZE TABLE book; Table Op Msg_type Msg_text test.book optimize status OK CALL find('they'); heading SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; count(*) 28 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; count(*) 33876 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; count(*) 0 SET GLOBAL innodb_ft_num_word_optimize=10000; OPTIMIZE TABLE book; Table Op Msg_type Msg_text test.book optimize status OK OPTIMIZE TABLE book; Table Op Msg_type Msg_text test.book optimize status OK CALL find('they'); heading SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_DELETED; count(*) 0 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE; count(*) 0 SELECT count(*) FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE; count(*) 0 DROP TABLE book; DROP PROCEDURE find; SET GLOBAL innodb_optimize_fulltext_only=OFF; SET GLOBAL innodb_ft_aux_table=default; SET GLOBAL innodb_ft_num_word_optimize=default;