1

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?

3
  • 1
    Does "deleted" mean DELETE? Or SET deleted = 1? – Rick James May 24 '17 at 20:55
  • @RickJames after a DML DELETE is done, the docid is added to INFORMATION_SCHEMA.INNODB_FT_DELETED before it is removed from the internal full text index structure. – Riedsio May 24 '17 at 20:58
  • 1
    Sounds like a bug report is needed here: bugs.mysql.com – Rick James May 25 '17 at 1:59

Your Answer

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

Browse other questions tagged or ask your own question.