As part of a maintenance activity we have started deleting unwanted data from our database tables. This is being done to free up space and improve the I/O operation efficiency of our database tables, which has taken a step back lately.

After deleting 1 billion records from table xyz, would it be a good idea to run "Optimize table xyz" query. As per mysql documentation Optimize query (which is essentially a ALTER TABLE .. FORCE) uses ONLINE DDL. Online ddl is supposed to help us continue using a table (concurrent DML operations) when its being altered but what if a table has billions of records and optimize query takes couple of hours to complete. Again as per documentation all the DML operations will be logged in the temporary files when optimize is running and changes will be applied to table once its done.

Still is this a reliable thing to do on a table with billions of records ?