Do I need to reindex my table in mysql after delete?

Posted on

Question :

I have a table in mysql with a lot of INSERT and SELECT at every second. And there is a bulk delete of some older data once in a day. Do I need to reindex the table after delete? I want to increase the performance. Can anybody suggest some tips? Using ‘innodb’ as engine. Do I need to change it? I think its better for concurrent insert and select. pls give your suggestions. Do I need reindexing?

Thanks in advance..

Answer :

When there is a lot on insert and delete operations on a table, the table will be “fragmented”, and the statistics that are used to decide the execution plan of a query will not be accurate. So YES, it is recommended to optimize the table from time to time (Not necessarily after each delete, but when a significant changes happen)

If there is no update and you are deleting old data, you might think of creating daily tables with date on its name. You can create a view to query them and you can easily drop the old table.

Leave a Reply

Your email address will not be published. Required fields are marked *