Database size twice as large after mysql dump and re-import

Question

After exporting and re-importing a mysql database, the size of the database on disk appears to be quadrupling in size. Any ideas why this my be?

This is occurring when importing the sql dump right back into exactly the same mysql database. To show what I mean:

1. Export mysql database: mysqldump -u mysql_user -p database1 > dump.sql
2. Import mysql database (into same mysql server, on same host): mysql -u mysql_user -p database 2 < dump.sql
3. Check the database size using:

SELECT table_schema "Database Name",
 sum( data_length + index_length ) / 1024 / 1024 "database size in MB",
 sum( data_free )/ 1024 / 1024 "free space in MB"
FROM information_schema.TABLES
GROUP BY table_schema; 

Which then gives:

+--------------------+---------------------+------------------+
| Database Name | database size in MB | free space in MB |
+--------------------+---------------------+------------------+
| information_schema | 0.01074219 | 0.00000000 |
| database2 | 978.73327637 | 120.00000000 |
| database1 | 284.76471329 | 217.00003815 |
+--------------------+---------------------+------------------+
3 rows in set (0.52 sec)

4. Note how the database 2 is 3.5 times bigger than database 1.
5. If I do a checksum test, I get the same checksum so the data seems to be valid. Just it's taking up much more space on the disk!

mysql> use database1
;Database changed
mysql> checksum table wp_site;
+--------------------+------------+
| Table | Checksum |
+--------------------+------------+
| database1.wp_site | 2263483605 |
+--------------------+------------+
1 row in set (0.00 sec)
mysql> use database2
;Database changed
mysql> checksum table wp_site;
+---------------------+------------+
| Table | Checksum |
+---------------------+------------+
| database2.wp_site | 2263483605 |
+---------------------+------------+
1 row in set (0.00 sec)

So my questions:

1. Why is this happening?
2. Is it really taking up more space on the disk, or is this somehow a mis-report?
3. Will database 2 have performance issues since it's much larger in size (even though the data seems to be the same)?

Note: in reality I'm copying the data over to a new server, not duplicating it into the same server. I'm using the example of the same server here to avoid any other unknowns.

Update

After running mysqlcheck -o -A, the database size did shrink a little, but not that much:

+--------------------+-----------------+------------------+------------------+
| Database Name | data size in MB | index size in MB | free space in MB |
+--------------------+-----------------+------------------+------------------+
| information_schema | 0.00000000 | 0.01074219 | 0.00000000 |
| database2 | 568.22155762 | 358.74609375 | 6.00000000 |
| database1 | 174.76861954 | 109.99609375 | 215.00003815 |
+--------------------+-----------------+------------------+------------------+

• topshot: Possible duplicate of Different MySQL Datafile Sizes After Restoration
• Vérace: The only thing I can think of is indexes. They are also identical?
• Philᵀᴹ: Could the old db be using MYISAM and the new one INNODB? Just a stab in the dark!
• Patrick: I thought about MYISAM and INNODB, but I checked that as well and they are identical (I updated the question).
• Rick James: Please provide SHOW TABLE STATUS for each of the databases. And SHOW VARIABLES LIKE 'innodb%';

Answer 1

Run the following command so that free space is compressed, and indexes checked and optimized:

mysqlcheck -o -A

After that, try to execute the following query:

SELECT table_schema "Database Name", 
sum( data_length) / 1024 "data size in MB", 
sum( index_length) / 1024 "index size in MB", 
sum( data_free )/ 1024 / 1024 "free space in MB" 
FROM information_schema.TABLES GROUP BY table_schema;

Especially check if the index size in MB column is different between the 2 databases, and show us the results.

• Patrick: I've updated my original question with the output to this (after running mysqlcheck
• Luis Alberto Barandiaran: The data and index sizes are roughly proportional, so that doesnt seem to be the problem... maybe it’s a collation issue or different character sets. I would check on those