MySQL Tablespace: .ibd Files and Why Tables Do Not Shrink

Where is your data on disk, and why does a table not get smaller when you delete rows?

File-per-table

With the default innodb_file_per_table = ON, each InnoDB table has its own file in the database’s folder, named table_name.ibd (“InnoDB data”). It holds the table’s data and its indexes.

Text

PLAIN

datadir/
├── ibdata1               # the system tablespace (data dictionary pieces, change buffer, ...)
├── #innodb_redo/         # the redo log files
├── undo_001, undo_002    # undo tablespaces
├── mysql.ibd             # the data dictionary (MySQL 8)
└── shop/
    ├── customers.ibd
    ├── orders.ibd
    └── order_items.ibd

query.sql

SQL

SELECT @@datadir IS NOT NULL AS has_datadir, @@innodb_file_per_table AS file_per_table;

Output

OUTPUT

+-------------+----------------+
| has_datadir | file_per_table |
+-------------+----------------+
|           1 |              1 |
+-------------+----------------+
1 row in set

Advantages of file-per-table: you can reclaim disk space for one table, copy or move a table (transportable tablespaces), and the system tablespace stays small. This is the right setting today.

Space used by a table

query.sql

SQL

SET SESSION cte_max_recursion_depth = 100000;
CREATE TABLE logs (id INT AUTO_INCREMENT PRIMARY KEY, message VARCHAR(100) NOT NULL, created_at DATETIME NOT NULL);
INSERT INTO logs (message, created_at)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 50000)
SELECT CONCAT('Log message number ', i, ' with some padding to give the row a realistic size'), '2026-01-01 00:00:00' + INTERVAL i MINUTE FROM n;

ANALYZE TABLE logs;
SET SESSION information_schema_stats_expiry = 0;
SELECT table_name, ROUND(data_length / 1024 / 1024, 1) AS data_mb, ROUND(data_free / 1024 / 1024, 1) AS free_mb
FROM information_schema.tables
WHERE table_schema = 'learn' AND table_name = 'logs';

Output

OUTPUT

+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| learn.logs | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set

+------------+---------+---------+
| TABLE_NAME | data_mb | free_mb |
+------------+---------+---------+
| logs       |     5.5 |     4.0 |
+------------+---------+---------+
1 row in set

data_length is the space used by rows, index_length by secondary indexes, and data_free is space inside the file that is allocated but not used by any row.

Why deleting rows does not shrink the file

query.sql

SQL

DELETE FROM logs WHERE id <= 40000;
ANALYZE TABLE logs;
SELECT COUNT(*) AS rows_left FROM logs;
SELECT table_name, ROUND(data_length / 1024 / 1024, 1) AS data_mb, ROUND(data_free / 1024 / 1024, 1) AS free_mb
FROM information_schema.tables
WHERE table_schema = 'learn' AND table_name = 'logs';

Output

OUTPUT

+------------+---------+----------+----------+
| Table      | Op      | Msg_type | Msg_text |
+------------+---------+----------+----------+
| learn.logs | analyze | status   | OK       |
+------------+---------+----------+----------+
1 row in set

+-----------+
| rows_left |
+-----------+
|     10000 |
+-----------+
1 row in set

+------------+---------+---------+
| TABLE_NAME | data_mb | free_mb |
+------------+---------+---------+
| logs       |     5.5 |     4.0 |
+------------+---------+---------+
1 row in set

Four fifths of the rows are gone, but the file is the same size. InnoDB marks the freed pages as available for reuse within the same table, but does not give them back to the operating system. New inserts reuse that space, so for a table that keeps growing it is not a problem. For a table that shrinks for good, such as after a big purge, you can reclaim it.

OPTIMIZE TABLE: rebuild and reclaim

query.sql

SQL

OPTIMIZE TABLE logs;
SELECT table_name, ROUND(data_length / 1024 / 1024, 1) AS data_mb, ROUND(data_free / 1024 / 1024, 1) AS free_mb
FROM information_schema.tables
WHERE table_schema = 'learn' AND table_name = 'logs';

Output

OUTPUT

+------------+----------+----------+-------------------------------------------------------------------+
| Table      | Op       | Msg_type | Msg_text                                                          |
+------------+----------+----------+-------------------------------------------------------------------+
| learn.logs | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| learn.logs | optimize | status   | OK                                                                |
+------------+----------+----------+-------------------------------------------------------------------+
2 rows in set

+------------+---------+---------+
| TABLE_NAME | data_mb | free_mb |
+------------+---------+---------+
| logs       |     1.5 |     2.0 |
+------------+---------+---------+
1 row in set

For InnoDB, OPTIMIZE TABLE is mapped to ALTER TABLE ... FORCE plus ANALYZE: it rebuilds the table into a new compact file, and swaps it in. The message “Table does not support optimize, doing recreate + analyze instead” is normal for InnoDB. The rebuild:

needs free disk space about the size of the table,
takes time proportional to the table size,
is mostly online (reads and writes continue) in recent versions, with a short lock at the start and end,
also refreshes statistics.

Do it after a big purge, not routinely. Fragmentation of a normal table is rarely a problem worth the effort.

Tablespaces

Tablespace	What it holds
File-per-table (`.ibd`)	One table with its indexes
System (`ibdata1`)	Change buffer and, in older versions, much more. In MySQL 8 the data dictionary lives in `mysql.ibd`
Undo (`undo_001`…)	Undo logs. Can be truncated automatically (`innodb_undo_log_truncate`)
General	A shared tablespace you create with `CREATE TABLESPACE`, holding several tables
Temporary	Session temporary tables

query.sql

SQL

SELECT name, space_type, row_format, page_size FROM information_schema.innodb_tablespaces WHERE name = 'learn/logs';

Output

OUTPUT

+------------+------------+------------+-----------+
| name       | space_type | row_format | page_size |
+------------+------------+------------+-----------+
| learn/logs | Single     | Dynamic    |     16384 |
+------------+------------+------------+-----------+
1 row in set

Row formats

The row format controls how rows are laid out inside pages:

Format	Notes
`DYNAMIC`	Default since MySQL 5.7. Long `VARCHAR`/`TEXT`/`BLOB` values are stored off-page with only a 20-byte pointer in the row. The right choice
`COMPRESSED`	Like `DYNAMIC`, with page compression. Saves disk, costs CPU
`COMPACT`, `REDUNDANT`	Older formats, kept for compatibility. Avoid in new tables

Compression

You have two options to save disk space:

ROW_FORMAT=COMPRESSED with KEY_BLOCK_SIZE: compresses pages inside the buffer pool too, with more CPU use.
Transparent page compression (COMPRESSION='zlib' or 'lz4'): compresses pages when writing them to disk, using the file system’s sparse file support. It saves disk space, not memory.

Both are worth testing for large, text-heavy, rarely changed tables (logs, archives). For a normal OLTP table, it is usually not worth the complexity.

Encryption at rest

ENCRYPTION='Y' encrypts a table’s tablespace on disk, with keys managed by a keyring plugin or component. It protects the data files and backups if someone gets the disk, not against someone with database access (Security Basics).

Sizing and planning

Estimate table size: rows x average row size, plus about 30 to 50 percent for indexes and free space.
A table with many secondary indexes can be larger than its data. Look at index_length.
Disk space must also cover redo logs, undo logs, binary logs (keep them under control with binlog_expire_logs_seconds), temporary files for big sorts and backups.
Monitor free disk space, and alert early. A full disk is a bad way to find out.

lightbulb

Free space inside a table is not wasted

data_free is reused by new rows. Rebuild with OPTIMIZE TABLE only after a big, permanent shrink, and only when you really need the disk space back.