Table maintenance

Right-click a table, a group of tables, or a database in the sidebar and pick Maintenance to run one of four operations: CHECK, OPTIMIZE, REPAIR or ANALYZE.

The four names are MySQL's, and they are the vocabulary the interface speaks. Every other engine maps them onto its own equivalent, so the same menu item means the same intent wherever you run it.

What each one is for

• CHECK — looks for structural and data errors. Changes nothing, so it is always safe to run.
• OPTIMIZE — reorganises the table and reclaims unused space. The table is rewritten.
• REPAIR — attempts to fix a damaged table. Run it only after CHECK has reported a problem.
• ANALYZE — refreshes the key statistics the query planner relies on. Fast and harmless, and the usual first thing to try when a query that used to be quick has become slow.

What actually runs

	CHECK	OPTIMIZE	REPAIR	ANALYZE
MySQL / MariaDB	CHECK TABLE	OPTIMIZE TABLE	REPAIR TABLE	ANALYZE TABLE
PostgreSQL	—	VACUUM (FULL, ANALYZE)	REINDEX TABLE	ANALYZE
SQLite	PRAGMA integrity_check *	VACUUM *	REINDEX	ANALYZE
SQL Server	DBCC CHECKTABLE	ALTER INDEX ALL … REBUILD	—	UPDATE STATISTICS

* On SQLite these two apply to the whole database file rather than to one table, so they run once per invocation no matter how many tables you selected.

Where an engine has no equivalent, the table is reported as skipped with the reason. It is never quietly substituted with something else:

• PostgreSQL has no core equivalent of CHECK TABLE.
• SQL Server repair needs DBCC CHECKDB with a repair option in single-user mode, and can discard data. That is not something to run from a context menu.

Before you run it

OPTIMIZE and REPAIR rewrite the table and hold a lock for the duration. On a large table that can take a long time, and anything trying to read the table waits. The dialog warns you before these two, and it is worth heeding on a production server during working hours.

CHECK and ANALYZE are cheap by comparison and can be run whenever you like.

Reading the result

The result grid has one row per message the server produced: Table, Operation, Status, Message.

The message is whatever the server said, verbatim, or the statement that was run. On PostgreSQL that means an OPTIMIZE reports VACUUM (FULL, ANALYZE) "t" — so the mapping from the MySQL vocabulary is visible rather than implied.

Statuses are ok, info, warning, error and skipped. On InnoDB tables MySQL commonly answers OPTIMIZE with an info note saying it did a rebuild plus analyze instead; that is normal and not a failure.

A table that fails becomes an error row and the run continues with the rest, so one broken table does not hide the state of everything else.

Selecting more than one table

Ctrl-click or shift-click tables in the sidebar and the menu applies to the whole selection. On a tables group it covers every table in it, and on a database node it covers every table in the database — including tables you have not expanded yet.