Following the MySQL 5.6 documentation (Example 14.5), I am under the impression that by setting innodb_optimize_fulltext_only=1 and optimizing a table, both the INNODB_FT_INDEX_CACHE and INNODB_FT_DELETED should decrease (as DML gets "flushed"/"committed").
However, this is not the observed behavior; I have millions of "deleted" documents that are not being touched (and I am suffering an ever increasing performance hit for my fulltext searches).
MYSQL> SET GLOBAL innodb_optimize_fulltext_only=1;
Query OK, 0 rows affected (0.00 sec)
MYSQL> SET GLOBAL innodb_ft_aux_table = 'my_db/my_table';
Query OK, 0 rows affected (0.00 sec)
MYSQL> select count(*) from INNODB_FT_INDEX_CACHE;
+----------+
| count(*) |
+----------+
| 860 |
+----------+
1 row in set (0.00 sec)
MYSQL> select count(*) from INNODB_FT_BEING_DELETED;
+----------+
| count(*) |
+----------+
| 6053404 |
+----------+
1 row in set (3.63 sec)
MYSQL> select count(*) from INNODB_FT_DELETED;
+----------+
| count(*) |
+----------+
| 6070724 |
+----------+
1 row in set (3.63 sec)
MYSQL> OPTIMIZE TABLE my_db.my_table;
+----------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+----------------+----------+----------+----------+
| my_db.my_table | optimize | status | OK |
+----------------+----------+----------+----------+
1 row in set (5.30 sec)
MYSQL> select count(*) from INNODB_FT_INDEX_CACHE;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
MYSQL> select count(*) from INNODB_FT_BEING_DELETED;
+----------+
| count(*) |
+----------+
| 6053404 |
+----------+
1 row in set (3.96 sec)
MYSQL> select count(*) from INNODB_FT_DELETED;
+----------+
| count(*) |
+----------+
| 6070820 |
+----------+
1 row in set (3.58 sec)
How can I get MySQL to take care of these deleted records?
