MySQL OPTIMIZE TABLE, ANALYZE TABLE and CHECK TABLE

InnoDB looks after itself well, so you do not need nightly maintenance on every table. But a few statements are worth knowing, because they fix specific problems: bad query plans from stale statistics, space that is not given back after large deletes, and suspected corruption.

ANALYZE TABLE: refresh the statistics

The optimizer chooses plans from statistics about each table and index (row counts, how many different values a column has). InnoDB updates them automatically, but after a large change they can be stale, and the optimizer may pick a poor plan (Index Selectivity).

query.sql

SQL

SET SESSION cte_max_recursion_depth = 30000;
CREATE TABLE events (id INT AUTO_INCREMENT PRIMARY KEY, kind VARCHAR(10) NOT NULL, created DATE NOT NULL, KEY idx_kind (kind));
INSERT INTO events (kind, created)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 5000)
SELECT ELT(1 + i % 3, 'click', 'view', 'buy'), DATE_ADD('2026-01-01', INTERVAL i % 90 DAY) FROM n;

ANALYZE TABLE events;

Output

OUTPUT

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

ANALYZE TABLE is fast and safe. For InnoDB it takes samples of the index pages, so it does not read the whole table, and it does not block normal reads and writes for long. Run it after loading or deleting a large part of a table, and when a query plan suddenly looks wrong. Look at the index statistics it refreshed:

query.sql

SQL

SELECT index_name, column_name, cardinality > 0 AS has_cardinality FROM information_schema.statistics WHERE table_schema = DATABASE() AND table_name = 'events' ORDER BY index_name;

Output

OUTPUT

+------------+-------------+-----------------+
| INDEX_NAME | COLUMN_NAME | has_cardinality |
+------------+-------------+-----------------+
| idx_kind   | kind        |               1 |
| PRIMARY    | id          |               1 |
+------------+-------------+-----------------+
2 rows in set

(cardinality is an estimate of the number of distinct values, so its exact value changes between runs. Do not rely on it as a number.) MySQL 8 also lets you create histograms for columns that are not indexed, so that the optimizer knows how values are spread:

query.sql

SQL

1
2

ANALYZE TABLE events UPDATE HISTOGRAM ON kind WITH 8 BUCKETS;
SELECT column_name, JSON_UNQUOTE(JSON_EXTRACT(histogram, '$."histogram-type"')) AS type FROM information_schema.column_statistics WHERE schema_name = DATABASE() AND table_name = 'events';

Output

OUTPUT

+--------------+-----------+----------+-------------------------------------------------+
| Table        | Op        | Msg_type | Msg_text                                        |
+--------------+-----------+----------+-------------------------------------------------+
| learn.events | histogram | status   | Histogram statistics created for column 'kind'. |
+--------------+-----------+----------+-------------------------------------------------+
1 row in set

+-------------+-----------+
| COLUMN_NAME | type      |
+-------------+-----------+
| kind        | singleton |
+-------------+-----------+
1 row in set

Histograms help the optimizer’s estimates for WHERE conditions on unindexed columns. They are not updated automatically: refresh them after the data changes a lot. (They do not change plans for columns that already have an index.)

OPTIMIZE TABLE: reclaim space

When you DELETE many rows, InnoDB does not shrink the file. The freed pages stay in the tablespace and are reused by later inserts. A table that held 10 million rows and now holds 1 million still occupies the old space on disk. OPTIMIZE TABLE rebuilds the table and its indexes, packing the data and returning unused space.

A real demonstration: fill a table, delete 90% of the rows, and watch the size:

query.sql

SQL

SET SESSION information_schema_stats_expiry = 0;
SET SESSION cte_max_recursion_depth = 30000;

CREATE TABLE big (id INT AUTO_INCREMENT PRIMARY KEY, payload VARCHAR(200) NOT NULL);
INSERT INTO big (payload)
WITH RECURSIVE n AS (SELECT 1 AS i UNION ALL SELECT i + 1 FROM n WHERE i < 20000)
SELECT REPEAT('x', 150) FROM n;
ANALYZE TABLE big;
SELECT data_length INTO @size_full FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'big';

DELETE FROM big WHERE id % 10 <> 0;
ANALYZE TABLE big;
SELECT data_length INTO @size_after_delete FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'big';

OPTIMIZE TABLE big;
SELECT data_length INTO @size_after_optimize FROM information_schema.tables WHERE table_schema = DATABASE() AND table_name = 'big';

SELECT @size_after_delete >= @size_full AS delete_did_not_shrink_file,
       @size_after_optimize < @size_after_delete / 2 AS optimize_returned_space;

Output

OUTPUT

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

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

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

+----------------------------+-------------------------+
| delete_did_not_shrink_file | optimize_returned_space |
+----------------------------+-------------------------+
|                          1 |                       1 |
+----------------------------+-------------------------+
1 row in set

Deleting 90% of the rows did not make the table smaller. OPTIMIZE TABLE did. Notice the message for InnoDB:

query.sql

SQL

OPTIMIZE TABLE big;

Output

OUTPUT

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

For InnoDB, OPTIMIZE TABLE is mapped to ALTER TABLE ... FORCE (a rebuild) followed by ANALYZE. The note is normal and expected. The rebuild is an online operation in current versions (reads and writes continue), but it copies the whole table, so it needs free disk space about equal to the table size, takes time on big tables, and creates replication lag. Run it:

Not routinely on every table. A table that is deleted from and inserted into at a steady rate reuses its own free space.
After a one-off big delete or archiving, when you need the disk space back.
Outside the busiest hours, on a table that is not huge. For huge tables, use an online schema-change tool.

An alternative with the same effect is ALTER TABLE big ENGINE = InnoDB; (Converting Engines). Another way to drop old data without a rebuild: drop whole partitions (Partitioning).

CHECK TABLE: look for corruption

query.sql

SQL

CHECK TABLE events;

Output

OUTPUT

+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| learn.events | check | status   | OK       |
+--------------+-------+----------+----------+
1 row in set

CHECK TABLE reads the table and its indexes and reports OK or the problem. Use it when you suspect a problem (crash, disk error, odd query results). InnoDB uses checksums on every page, so real corruption normally shows up as an error in the error log when a bad page is read. If a table is corrupt, the best fix is to restore from a backup, or to dump what you can read and re-create the table.

REPAIR TABLE: MyISAM only

REPAIR TABLE applies to MyISAM (and ARCHIVE, CSV) tables. InnoDB does not support it. If you try it you get a plain message instead of a repair:

query.sql

SQL

REPAIR TABLE events;

Output

OUTPUT

+--------------+--------+----------+---------------------------------------------------------+
| Table        | Op     | Msg_type | Msg_text                                                |
+--------------+--------+----------+---------------------------------------------------------+
| learn.events | repair | note     | The storage engine for the table doesn't support repair |
+--------------+--------+----------+---------------------------------------------------------+
1 row in set

For a damaged InnoDB table, the recovery tools are innodb_force_recovery (to start the server far enough to dump data), backups, and the binary log (Point-in-Time Recovery).

mysqlcheck: the same from the shell

mysqlcheck runs these statements for every table, which makes it easy to use in a script or cron job:

terminal

BASH

mysqlcheck -u root -p --check shop
mysqlcheck -u root -p --analyze shop
mysqlcheck -u root -p --optimize shop
mysqlcheck -u root -p --all-databases --check

The real output from a MySQL 8.4 test server:

Output

OUTPUT

$ mysqlcheck --check shop
shop.customers                                     OK
shop.orders                                        OK

$ mysqlcheck --optimize shop
shop.customers
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK
shop.orders
note     : Table does not support optimize, doing recreate + analyze instead
status   : OK

Finding tables that need attention

Free space inside a table’s file, and the largest tables:

query.sql

SQL

SELECT table_name, ROUND(data_length / 1024 / 1024, 1) AS data_mb, ROUND(index_length / 1024 / 1024, 1) AS index_mb, ROUND(data_free / 1024 / 1024, 1) AS free_mb
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'sys', 'information_schema', 'performance_schema') AND table_type = 'BASE TABLE'
ORDER BY data_length + index_length DESC
LIMIT 10;

Output

OUTPUT

(a list of your largest tables, with free_mb showing space that OPTIMIZE could return)

A table with a large free_mb compared with its data_mb is a candidate for OPTIMIZE TABLE. Remember that data_free for a file-per-table InnoDB tablespace includes only space inside that table’s file. (Statistics here can be cached for up to 24 hours: set information_schema_stats_expiry = 0 in the session, as above, to see current values.)

A maintenance routine that makes sense

Task	How often
Check backups and restore drills	Regularly (Backup Strategy)
`ANALYZE TABLE`	After large data changes, and when a plan looks wrong
`OPTIMIZE TABLE`	After a big delete, when you need the space
`CHECK TABLE`	When you suspect a problem
Review the slow query log and unused indexes	Monthly (Slow Query Log)
Purge old data (archive or partition drop)	On a schedule (Changing Millions of Rows)
Disk space and growth	Alert, and review trends

lightbulb

Do not optimize on a schedule by habit

Rebuilding every table every night wastes I/O, can cause replication lag, and rarely helps InnoDB. Do it when a measurement says there is space to gain.