MySQL write performance after truncate & optimize Once a year we truncate a table of  several GBs and millions of rows and then run optimize in order to shrink the size of the corresponding ibd file on the file system.
However, as this is write intensive table, we are wondering whether running optimize is actually a good idea. Maybe it’s smarter to leave the file size as is and let MySQL reuse already allocated disk space.
Is it more efficient in terms of write performance to optimize the table in order to shrink the file size and then let it progressively grow again, or just reuse space previously allocated?
We use InnoDB as storage engine and MySQL 5.7 as DBMS.
TRUNCATE TABLE throws away the table and creates a new, empty table, with the same schema.  There is nothing left to OPTIMIZE.
If you are really throwing away the data, why did you keep it?  Or, maybe you would prefer to toss the &quot;oldest&quot; part of the data each week?  And do it with virtually no impact on writes?  If so, you have a classic case for PARTITIONing.  Use PARTITION BY RANGE(TO_DAYS(..)) and a weekly task to DROP the  oldest partition and deal with making a new partition.  Details:  http://mysql.rjweb.org/doc.php/partitionmaint
Be sure to have innodb_file_per_table=ON.
(If this does not address your Question, please elaborate on it.)