I have read several posts here in the SOPT about creating índices which got me a lot of doubts I had, but my main one is about FULL TEXT !
In a database that is exclusively targeted to ecommerce , where inserts , updates and deletes exist all the time. Do I often have to be recreating these indexes or mysql does this automatically?
MySQL does not recreate FULLTEXT indexes automatically.
Deletions of records that have index columns of this type are written to auxiliary tables, used as filters to return query results without deletions. Like advantage , deletions are quick and performative. However, the disadvantage is that the index size is not immediately reduced after deleting the records. To remove FULLTEXT index entries and improve query performance, you need to rebuild the index as per the instructions below.
To recreate a FULLTEXT index you need to enable innodb_optimize_fulltext_only and run 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)
More details can be found at documentation .