Do I need to reindex mysql table after bulk delete?

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 the storage engine. Do I need to change it? I think its better for concurrent insert and select. Please give your suggestions. Do I need to do reindexing?

Thanks in advance..

Do you need to optimize the tables when using InnoDB? Yes and no, depending on your workload and if you are encountering performance problems or not.

A shameless copy-paste from the MySQL documentation :


For InnoDB tables, OPTIMIZE TABLE is mapped to ALTER TABLE, which
rebuilds the table to update index statistics and free unused space in
the clustered index. This is displayed in the output of OPTIMIZE TABLE
when you run it on an InnoDB table, as shown here:

mysql> OPTIMIZE TABLE foo;
+----------+----------+----------+-------------------------------------------------------------------+
| Table    | Op       | Msg_type | Msg_text                                                          |
+----------+----------+----------+-------------------------------------------------------------------+
| test.foo | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.foo | optimize | status   | OK                                                                |
+----------+----------+----------+-------------------------------------------------------------------+

This operation does not use fast index creation. Secondary indexes are
not created as efficiently because keys are inserted in the order they
appeared in the primary key. See Section 14.14.6, “Limitations of Fast
Index Creation”.


InnoDB stores data using a page-allocation method and does not suffer
from fragmentation in the same way that legacy storage engines (such
as MyISAM) will. When considering whether or not to run optimize,
consider the workload of transactions that your server will process:



-
Some level of fragmentation is expected. InnoDB only fills pages 93% full, to leave room for updates without having to split pages.

-
Delete operations might leave gaps that leave pages less filled than desired, which could make it worthwhile to optimize the table.

-
Updates to rows usually rewrite the data within the same page, depending on the data type and row format, when sufficient space is
available. See Section 14.10.5, “How Compression Works for InnoDB
Tables” and Section 14.12.1, “Overview of InnoDB Row Storage”.

-
High-concurrency workloads might leave gaps in indexes over time, as InnoDB retains multiple versions of the same data due through its MVCC
mechanism. See Section 14.5.12, “InnoDB Multi-Versioning”.

StackExchange Database Administrators Q#82607, answer score: 13