mysql – Full Text Index for Ecommerce Database!

Question:

I read several posts here on SOPT about creating índices which took me out of many doubts I had, but my main one is about FULL TEXT !

In a database that is exclusively focused on ecommerce , where there are inserts , updates and deletes all the time. Do I have to frequently be rebuilding these indexes or does mysql do this automatically?

Answer:

MySQL does not automatically rebuild FULLTEXT indexes.

Deletions of records that have columns with an index of this type are recorded in auxiliary tables, used as a filter to return query results without the deletions. As an advantage , the deletions are fast and performative. However, the downside is that the index size is not immediately reduced after the records are deleted. To remove FULLTEXT index entries and improve query performance, you need to rebuild the index as instructed below.

To rebuild 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)    

Further details can be seen in the documentation .

Scroll to Top