MySQL 8 - optimize all tables + indexes

Question

I have a bunch of tables (InnoDB) within several databases (MySQL 8), that I run Garbage Collect scripts on, on daily basis, removing stale records that are no longer needed. All tables do have indexes on them.

Would it make sense / be at all beneficial to optimize all tables, say every week, to increase performance? If so, is there are good command to run, to do so?

I tried doing

sudo mysqlcheck -o --all-databases

But get the following error message:

note     : Table does not support optimize, doing recreate + analyze instead
status   : OK

Answer 1

Don't bother. InnoDB mostly takes care of itself. OPTIMIZE temporarily squeezes out some wasted space, but new waste occurs as inserts/etc happen. The performance benefit of OPTIMIZE is next to nil.

Answer 2

Those messages are normal for InnoDB. You can continue doing so.

The InnoDB Storage Engine performs OPTIMIZE TABLE mydb.mytable; like this

ALTER TABLE mydb.mytable ENGINE=InnoDB;
ANALYZE TABLE mydb.mytable;

I have mentioned this over the years:

• Sep 27, 2015 : How can I defragment tables in MySQL?
• Jun 24, 2015 : guideline for calling optimize table after alter table?
• Feb 27, 2013 : What is the difference between optimize table and analyze table in mysql
• Aug 04, 2011 : Optimizing InnoDB default settings

I recommend doing analyze daily or weekly

sudo mysqlcheck --analyze --all-databases

Then, do the optimize once a month or once a quarter.