SQL OPTIMIZE

What does the SQL OPTIMIZE statement do?

OPTIMIZE reorganizes and defragments tables to reclaim space and improve read performance.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL OPTIMIZE: Supported: MySQL, MariaDB, TiDB, Percona Server. Partially supported with different semantics: ClickHouse (OPTIMIZE TABLE), Amazon Redshift (VACUUM). Not supported: PostgreSQL, SQL Server, Oracle, SQLite.

SQL OPTIMIZE Full Explanation

OPTIMIZE is a maintenance statement that rewrites the physical storage of a table and updates its indexes. In MySQL and MariaDB it maps to ALTER TABLE ... FORCE, creating a new copy of the table, removing deleted rows, compressing data pages, and rebuilding all secondary indexes. The operation frees disk space from UPDATE and DELETE churn, improves index statistics, and can coalesce adjacent free space. It is blocking for MyISAM tables and online for InnoDB when innodbaoaoise DDL is enabled. When used with partitioned tables, each partition is rebuilt. The statement can be safely replicated by adding the NO_WRITE_TO_BINLOG | LOCAL modifier. Other systems offer similar commands under different names (VACUUM, ANALYZE, REINDEX). Run OPTIMIZE during low-traffic windows or on replicas to avoid lock contention.

SQL OPTIMIZE Syntax

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE table_name [, table_name ...];

SQL OPTIMIZE Parameters

NO_WRITE_TO_BINLOG - LOCAL keyword Prevents the statement from being written to the binary log, so replicas do not run it.
table_name - identifier One or more existing tables or views to optimize, comma-separated.

Example Queries Using SQL OPTIMIZE

-- Rebuild a single table
OPTIMIZE TABLE orders;

-- Optimize multiple tables at once
OPTIMIZE LOCAL TABLE customers, invoices;

-- Skip replication logging
OPTIMIZE NO_WRITE_TO_BINLOG TABLE session_store;

Expected Output Using SQL OPTIMIZE

Server returns an OK packet per table with columns Table, Op, Msg_type, and Msg_text
Msg_text shows status such as 'OK', 'Table is already up to date', or the amount of space recovered

Use Cases with SQL OPTIMIZE

Reclaim disk space after large DELETE operations
Refresh fragmented InnoDB secondary indexes
Improve range scans on heavily updated MyISAM tables
Periodic housekeeping in OLTP databases
Preparing a table before copying it to cold storage

Common Mistakes with SQL OPTIMIZE

Assuming OPTIMIZE is non-blocking on all storage engines
Running OPTIMIZE on InnoDB tables without enough free disk space (needs a full copy)
Using OPTIMIZE on systems where VACUUM or ALTER TABLE is the correct command
Expecting dramatic speedups on tables with little fragmentation

First Introduced In

MySQL 3.23

Frequently Asked Questions

When should I run OPTIMIZE TABLE?

Run it after bulk DELETE or UPDATE operations, or during scheduled maintenance to keep tables compact and statistics fresh.

Does OPTIMIZE lock the table?

For InnoDB tables with online DDL enabled it permits concurrent reads and writes; MyISAM tables are fully locked.

How much free disk space do I need?

Have enough free space for a temporary copy of the largest index or the entire table, whichever is greater.

Is OPTIMIZE the same as VACUUM in PostgreSQL?

No. VACUUM reclaims space and updates statistics in PostgreSQL, but the syntax and internal mechanics differ. Use OPTIMIZE only in MySQL-compatible systems.