Will this solution safely reduce the disk size of my database

Question

I need to reduce the size of my Civi database.

I've done a bunch of reading and have found what looks like a solution. Please will someone step in to advise if this solution (below) is the way to go, and if I can just run it on each of the db tables.

Identifying the problem tables

Mailing records are my primary issue and this extension worked well to remove old records. https://github.com/adixon/nz.co.fuzion.deleteoldbulkmailings

But as mentioned in Recommendations or Experiences on Archiving Mailing Data this has not freed-up as much the space as I'd hoped.

Using this helped identify the culprits:

SELECT 
table_schema AS `Database`, 
table_name AS `Table`, 
round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` 
FROM information_schema.TABLES WHERE table_schema = 'dbname' 
ORDER BY (data_length + index_length) DESC
LIMIT 20;

SO, IS THIS THE SOLUTION?

This link offers a solution to recover the disk space: https://dba.stackexchange.com/questions/48740/recover-the-disk-space-after-deleting-rows-from-table

The accepted solution in the post is to run this on the tables that have had rows removed:

USE mydb
DROP TABLE IF EXISTS mytablenew;
CREATE TABLE mytablenew LIKE mytable;
ALTER TABLE mytable RENAME mytableold;
INSERT INTO mytablenew SELECT * FROM mytableold;
ANALYZE TABLE mytablenew;
ALTER TABLE mytablenew RENAME mytable;
DROP TABLE mytableold;

I'm running the latest Drupal and Civi versions.

Answer 1

I don't think this is a solution you should employ without checking on a few things i.e. foreign keys, indexes, triggers etc. It's a risky way to do it. If you must find space and have cleared as much data as you can then the way to achieve the same results would be to backup and restore the database. This will ensure all the structures are maintained.

There are alternatives but it depends on the version of MySQL/MariaDB you're running so if you want to explore them then please do post the details.