mysqlinnodb

mysqldump creates more rows than the actual range of primary key


I have a table that is roughly 290,000 rows long. Before backup, it probably took <200 MB. When I created a backup of this table using mysqldump, the backup file takes ~800 MB, and when I reload from the backup file using mysql, I now see that it has ~430,000 rows, way more than the original table (I am checking via HeidiSQL UI). But if I do a query on the total range of the primary key, it is the same as the old table (~290,000). What could have possibly gone wrong?

Here is the CREATE code for the particular table in concern. It is just a list of variables (of DECIMAL type)

    CREATE TABLE `ciceroout` (
    `runID` INT(11) NOT NULL AUTO_INCREMENT,
    `IterationNum` DECIMAL(20,10) NULL DEFAULT NULL,
    `IterationCount` DECIMAL(20,10) NULL DEFAULT NULL,
    `RunningCounter` DECIMAL(20,10) NULL DEFAULT NULL,
    \* more 100 variables like this *\
    PRIMARY KEY (`runID`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=287705
;

EDIT: Here is the actual dump and restore commands I used. Our database has six tables, and I already dumped one table so here I am dumping the remaining five tables.

dump tables :

 mysqldump -u root --single-transaction=true --verbose -p [dbname] --ignore-table=[dbname].images > \path\[backupname].sql

restore tables (after dropping the original database, and starting an empty one):

mysql -u root -p [db name] < \path\[backupname].sql

and here is what I see on HeidiSQL UI enter image description here


Solution

  • If you wonder about the big export file: thats normal.
    The export is stored in a human readable format (SQL), while the actual data on the tablespace is stored in a much more effficient data structures (B+Tree and others)

    Regarding the table statistics HeidiSQL is showing you:
    For InnoDB, the "number of rows" statistic is just an approximation.

    The result of COUNT(*) gives you the real amount of rows, which is matching the original, right?

    The approximation will change over time and get better as you start working with the data.

    The MySQL manual page for SHOW TABLE STATUS states:

    The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.