0

I have a table which has very minimal data. However the index_length is very very huge, because of frequent DML operations on this table.

Ex :

+-----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| Name                  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation       | Checksum | Create_options | Comment |
+-----------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+
| TABLE_NAME            | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |   1046790144 |   6291456 |           NULL | 2018-01-29 18:29:41 | NULL        | NULL       | utf8_general_ci |     NULL |                |         |

To manually fix this, we can use OPTIMIZE TABLE command to re-index a table.

How can we configure it on db server side, so that re-index happens automatically based on some configuration or threshold ?

0

There is no way to auto-reindex.

As you point out, OPTIMIZE TABLE (or certain variants of ALTER TABLE) will rebuild the index(es).

Instead, please elaborate on what is going on that led to the situation. Perhaps there is another approach that can avoid the 'problem'.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.