3
I read several posts here on SOPT about creating índices which left me with many doubts I had, but my main one is about FULL TEXT!
In a database that is exclusively aimed at ecommerce, where there are inserts, updates and deletes all the time. I often have to be recreating these indexes or mysql does this automatically?
1
Mysql does not automatically recreate FULLTEXT indexes.
Deletions of records that have columns with index of this type are saved in auxiliary tables, used as a filter to return the results of queries without deletions. Like head start, deletions are fast and performative. However, the downside is that the index size is not immediately reduced after deletion of records. To remove FULLTEXT index entries and improve the performance of queries, it is necessary to rebuild the index, as instructed below.
To recreate a FULLTEXT index you need to enable innodb_optimize_fulltext_only and rotate OPTIMIZE TABLE:
mysql> set GLOBAL innodb_optimize_fulltext_only=ON;
Query OK, 0 rows affected (0.01 sec)
mysql> OPTIMIZE TABLE opening_lines;
+--------------------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+--------------------+----------+----------+----------+
| test.opening_lines | optimize | status | OK |
+--------------------+----------+----------+----------+
1 row in set (0.01 sec)
Further details can be found at documentation.
Browser other questions tagged mysql database query índices
You are not signed in. Login or sign up in order to post.