Table Maintenance
Open in CalíopeAnalyze, check, optimize or repair the selected tables, with the operations the server has.
Where it is: Workspace › Tools › Table Maintenance
Open Table Maintenance from the workspace sidebar. Select tables in the tree on the left (a database's checkbox selects all its tables, or tick them one by one), choose the operation at the top — ANALYZE, CHECK, CHECKSUM, OPTIMIZE or REPAIR — and click Run. Next to the button you see how many tables are selected.
The results appear below, one row for each message the server returns for each table: warnings are highlighted in orange and errors in red. On InnoDB, OPTIMIZE answers with a note: it rebuilds the table and analyzes it instead. CHECKSUM gives one number per table; the same number on two servers means the same rows. REPAIR only runs on tables whose storage engine can repair itself — MyISAM, Aria, ARCHIVE and CSV. Before you run it, a line under the modifiers says how many of the selected tables will be skipped, and each one gets a row saying why; on InnoDB the row suggests OPTIMIZE, which rebuilds the table. On the Mac, click a column header to sort the results; Clear results empties the list. While an operation runs, a progress bar and Cancel appear next to Run; tables are processed one at a time, so cancelling stops before the next one.
The tree has a filter by name in its header, just like the SQL Editor: type part of a database or table name to narrow the list. Each database loads its tables the first time you expand it, so if you filter with databases still unexpanded, the footer tells you how many are missing and offers to load them. Select all sweeps every database on the server except the server's own system databases, which you can still tick by hand, and may take a few seconds. If the server rejects the operation on a table, that table gets an error row with the server's message.
SQL Server
There are three operations: ANALYZE (UPDATE STATISTICS), CHECK (DBCC CHECKTABLE) and OPTIMIZE (ALTER INDEX ALL … REBUILD). There is no REPAIR —it needs the database in single-user mode and can lose data— and no CHECKSUM. OPTIMIZE locks the table while it rebuilds: rebuilding online exists only in the Enterprise edition, and not with spatial indexes.
PostgreSQL
There are two operations: ANALYZE and OPTIMIZE, which runs VACUUM and then REINDEX TABLE CONCURRENTLY, so it gives two rows per table; neither stops the table. There is no CHECK —the core has nothing like it—, no REPAIR, no CHECKSUM and no modifiers. VACUUM FULL, the only one that gives disk space back to the system, is left out on purpose: it locks the whole table while it rewrites it. On a server older than 12, OPTIMIZE stops at the VACUUM and the index row says why.
Keywords: ANALYZE, CHECK, OPTIMIZE, REPAIR, maintenance, CHECKSUM