MySQL – Why Does mysqldump Restore to a Smaller Size?

MySQLmysqldump

I am using MySQL and mysqldump v8.0.19 on Windows 10. I have an InnoDB database, old. I use mysqldump (and the --no-create-db option) to dump its tables. I then create a new database, new, and import the dumped tables with

mysql -u myName -p new < old.sql

This procedure seems to work in the sense that the tables in old and new seem to have exactly the same data and exactly the same keys. CHECK TABLE reveals no problems with the tables in new, and commands like SELECT * FROM old.table1 UNION SELECT * FROM new.table1 reveal no discrepancies between the old and new tables.

But for all of that, the new tables are smaller. Specifically, this command shows that they're smaller in both data_length and index_length:

SELECT concat( table_schema, '.', table_name ) table_name,
  concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length,
  concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length, 
  concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size
  FROM information_schema.TABLES
  ORDER BY ( data_length + index_length ) DESC;

Why are the restored tables smaller (and should I be concerned)?

A [https://stackoverflow.com/questions/24707770/mysql-database-dump-restores-to-smaller-size](question like this) was asked six years ago on Stack Overflow, but it never got any replies.

Best Answer

InnoDB stores records inside pages (by default each page size is equal to 16 KB). For different reasons you may have partially filled pages (for example, after you deleted some records). When you restore database using mysqldump your rebuild tables from scratch and receive less fragmented version of your database. You can use OPTIMIZE TABLE command to defragment table in your old database and release some space.

Related Question