Table size lower after copy
Just out of curiosity, how come the disk size of a table is lower than the original after a 1:1 copy?
Below are the commands I used. As you see the file size for the new table is much lower than the old. Both tables use InnoDB as engine.
$ sudo ls -lart /var/lib/mysql/db_allCountries/
[sudo] password for thommym:
total 7995532
-rw-rw----. 1 mysql mysql 61 Jul 7 15:30 db.opt
-rw-rw----. 1 mysql mysql 42084 Jul 7 17:08 allCountries.frm
-rw-rw----. 1 mysql mysql 4592762880 Jul 7 17:41 allCountries.ibd
drwxr-xr-x. 14 mysql mysql 4096 Nov 18 11:36 ..
-rw-rw----. 1 mysql mysql 42164 Nov 18 12:53 my_allCountries.frm
drwx------. 2 mysql mysql 4096 Nov 18 12:53 .
-rw-rw----. 1 mysql mysql 3594518528 Nov 18 14:07 my_allCountries.ibd
mysql> INSERT INTO my_allCountries (`geonameid`, `name`, `asciiname`, `alternatenames`, `latitude`, `longitude`, `feature_class`, `feature_code`, `country_code`, `cc2`, `admin1_code`, `admin2_code`, `admin3_code`, `admin4_code`, `population`, `elevation`, `dem`, `timezone`, `modification_date`) SELECT `geonameid`, `name`, `asciiname`, `alternatenames`, `latitude`, `longitude`, `feature_class`, `feature_code`, `country_code`, `cc2`, `admin1_code`, `admin2_code`, `admin3_code`, `admin4_code`, `population`, `elevation`, `dem`, `timezone`, `modification_date` FROM allCountries;
Query OK, 10259049 rows affected (32 min 34.92 sec)
Records: 10259049 Duplicates: 0 Warnings: 0
mysql> describe allCountries;
+-------------------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+----------------+------+-----+---------+-------+
| geonameid | int(11) | NO | PRI | NULL | |
| name | char(200) | NO | | | |
| asciiname | varchar(200) | YES | | NULL | |
| alternatenames | varchar(10000) | YES | | NULL | |
| latitude | float(10,6) | YES | | NULL | |
| longitude | float(10,6) | YES | | NULL | |
| feature_class | char(1) | YES | | NULL | |
| feature_code | varchar(10) | YES | | NULL | |
| country_code | char(2) | YES | | NULL | |
| cc2 | varchar(200) | YES | | NULL | |
| admin1_code | varchar(20) | YES | | NULL | |
| admin2_code | varchar(80) | YES | | NULL | |
| admin3_code | varchar(20) | YES | | NULL | |
| admin4_code | varchar(20) | YES | | NULL | |
| population | int(8) | YES | | NULL | |
| elevation | int(8) | YES | | NULL | |
| dem | int(8) | YES | | NULL | |
| timezone | varchar(40) | YES | | NULL | |
| modification_date | date | YES | | NULL | |
+-------------------+----------------+------+-----+---------+-------+
19 rows in set (0.39 sec)
mysql> describe my_allCountries;
+-------------------+----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+----------------+------+-----+---------+-------+
| geonameid | int(11) | NO | PRI | NULL | |
| name | char(200) | NO | | | |
| asciiname | varchar(200) | YES | | NULL | |
| alternatenames | varchar(10000) | YES | | NULL | |
| latitude | float(10,6) | YES | | NULL | |
| longitude | float(10,6) | YES | | NULL | |
| feature_class | char(1) | YES | | NULL | |
| feature_code | varchar(10) | YES | | NULL | |
| country_code | char(2) | YES | | NULL | |
| cc2 | varchar(200) | YES | | NULL | |
| admin1_code | varchar(20) | YES | | NULL | |
| admin2_code | varchar(80) | YES | | NULL | |
| admin3_code | varchar(20) | YES | | NULL | |
| admin4_code | varchar(20) | YES | | NULL | |
| population | int(8) | YES | | NULL | |
| elevation | int(8) | YES | | NULL | |
| dem | int(8) | YES | | NULL | |
| timezone | varchar(40) | YES | | NULL | |
| modification_date | date | YES | | NULL | |
| slump | int(8) | YES | | NULL | |
+-------------------+----------------+------+-----+---------+-------+
20 rows in set (0.01 sec)
$ mysqld --version
mysqld Ver 5.6.22 for Linux on x86_64 (MySQL Community Server (GPL))