Querious Docs
Account

Table Operations

Analyze, optimize, check, repair, vacuum, and truncate without writing the statements.

The Table Operations submenu runs maintenance statements on the selected tables. It's in the primary sidebar's contextual menu, the Table Content view's contextual menu, and the Connection menu.

Operations can take a long time to finish, so they run as background tasks with a progress panel and can be applied to several tables at once. You can tell Querious to "Stop Waiting" if you want to move onto other tasks while the server finishes.

MySQL #

Operation Statement Use it to
Analyze ANALYZE TABLE Recompute the key statistics the optimizer uses to choose a plan. Try this first when a query stops picking a good index.
Check CHECK TABLE Look for errors. Read-only; it reports problems without fixing them.
Optimize OPTIMIZE TABLE Reclaim space and defragment, such as after deleting a large fraction of rows.
Repair REPAIR TABLE Attempt to fix a damaged table. MyISAM and a few other engines only; InnoDB has its own recovery.
Flush FLUSH TABLE Close and reopen the table, flushing its cached copy.
Truncate… TRUNCATE TABLE Remove every row. Asks first, and offers Ignore Foreign Key Constraints for tables other tables reference.

PostgreSQL #

Operation Statement Use it to
Analyze… ANALYZE Update the planner's statistics.
Vacuum… VACUUM Reclaim space from dead tuples. The confirmation offers Full Vacuum (VACUUM FULL).
Truncate… TRUNCATE Remove every row.

Materialized views have their own refresh controls in the Structure view. Refreshing re-runs the query and repopulates it; clearing its data leaves it defined but empty.

Recipes #

To empty a table that other tables reference

  1. Choose Table OperationsTruncate….
  2. If it's refused because of references, turn on Ignore Foreign Key Constraints.
  3. Make sure the referencing rows are going too, or they'll be left pointing at nothing.

To reclaim space after deleting many rows

  1. Note the table's size in Table Info.
  2. On MySQL run Optimize. On PostgreSQL run Vacuum, adding Full Vacuum only if you can afford the exclusive lock.
  3. Check the size afterwards.

To fix a query that suddenly chooses a bad plan

  1. Confirm the plan with Explain Query in the query editor.
  2. Select the tables involved and choose Table OperationsAnalyze.
  3. Explain again. Stale statistics after a large data change are a common cause.