InnoDB is telling you that the data pages are the same size.
The DELETE you ran simply makred the rows delete within the associated data pages.
You have to run
OPTIMIZE TABLE big_data;
ALTER TABLE big_data ENGINE=InnoDB;
to shrink it.
Since you inserted data into a table with no indexes, that explains 0 for
data_free column is really superfluous when innodb_file_per_table is 1 (Thta's the default for MySQL 5.6). The
data_free column was used to give you the total space free inside ibdata1 when innodb_file_per_table = 0 and all data and indexes were inside ibdata1. Since innodb_file_per_table = 1 and you loaded data into MySQL from the its beginning, I believe 0 will always appear for
data_free for every InnoDB table in INFORMATION_SCHEMA.TABLES.
Here is something else to consider: The INFORMATION_SCHEMA.TABLES does not properly update because of the Storage Engine's ineraction with it.
For example, I noticed you used
select count(*) from big_data;
Why did you not use
select data_length, index_length, data_free, table_rows
where TABLE_NAME = 'big_data';
Using this Pictorial Representation of the InnoDB Architecture
try imagine what is happening when you delete 4 million rows:
- Drag data pages for the 4M rows into the buffer pool
- Capture those rows in the Rollback Segments
- Mark the rows delete inside those memory-resident data pages
- Write the changes into the Double Write Buffer
- Migrate changed data pages through the Log Buffer to commit the changes back to the
- Write Redo Log Info into the Transaction Logs (ib_logfile0, ib_logfile1).
- Clean up the Rollback Segments upon the completion of the DELETE.
In light of this, it would take a considerable amount of time for the INFORMATION_SCHEMA.TABLES to catch up with all these changes.
Please note that you could never reliably extract exact row counts from INFORMATION_SCHEMA.TABLES. The MySQL Query Optimizer always approximates row counts for InnoDB tables by scanning non-leaf BTREE nodes. I have written about this consistently in the DBA StackExchange for almost 3 years.
Therefore, it is not surprising that other metadata aspects of InnoDB do not update column info in INFORMATION_SCHEMA.TABLES effectively .
What if the table was MyISAM ???
MyISAM does not have to jump through all these hoops like InnoDB. Notwithstanding, even if the
big_data table was MyISAM, the
.MYD file would remain the same size after the DELETE. You would still have to run
OPTIMIZE TABLE big_data; to shrink the