MySQL OPTIMIZE TABLE: When and How to Use It

MySQL’s OPTIMIZE TABLE rebuilds a table’s data and index storage to reclaim wasted space and improve query performance. You run it with a single statement (OPTIMIZE TABLE your_table_name;) and it returns a result set showing the operation’s status. This article explains what it actually does, when it helps, when it doesn’t, and how to use it safely.

What OPTIMIZE TABLE Does Behind the Scenes

When you delete rows or update variable-length columns (like VARCHAR or TEXT), MySQL leaves behind fragmented space in the table’s data file. Over time, this fragmentation can cause the table to use more disk space than necessary and slow down full table scans. OPTIMIZE TABLE performs a physical rebuild:

• It creates a new copy of the table with data packed contiguously.
• It rebuilds all indexes, sorting the index entries for faster lookups.
• It updates table statistics that the query optimizer uses to choose execution plans.

For InnoDB tables, the operation is actually implemented as an ALTER TABLE ... FORCE that recreates the table in place (using the ALGORITHM=INPLACE method in MySQL 5.7 and later). For MyISAM tables, it performs a full rebuild and also defragments the indexes.

When Should You Run OPTIMIZE TABLE?

You don’t need to run it on a schedule like a cron job. The right time is when you see a real symptom:

• After mass deletions – e.g., you deleted 80% of a table’s rows and the data file is still huge.
• After heavy updates – especially on tables with many variable-length columns that change size frequently.
• When information_schema.TABLES shows high fragmentation – compare DATA_FREE (allocated but unused bytes) to DATA_LENGTH. If DATA_FREE is a large percentage, optimization can help.
• After importing/exporting large data sets – the import may leave the table in a suboptimal physical state.

For example, a table with 10 million rows that you trim to 2 million might still occupy the same disk space. Running OPTIMIZE TABLE can shrink the file and speed up scans.

How to Run OPTIMIZE TABLE Safely

Before running the command, consider these steps to avoid downtime and data loss:

1. Check the table size – SELECT table_name, ROUND(data_length/1024/1024,2) AS size_mb FROM information_schema.tables WHERE table_schema = 'your_db';
2. Ensure you have enough free disk space – InnoDB’s in-place algorithm needs extra space for the new copy (roughly the size of the table plus indexes). MyISAM also creates a temporary copy.
3. Run during low traffic – The operation locks the table (even with in-place rebuild, it acquires an exclusive lock for part of the process).
4. Use the ALGORITHM and LOCK clauses (MySQL 5.7+): OPTIMIZE TABLE your_table ALGORITHM=INPLACE, LOCK=NONE; – this allows concurrent reads and writes in many cases, but if it fails, fall back to the default.
5. Monitor progress – In MySQL 8.0, you can check performance_schema for stage events, or simply watch the table size shrink.

For very large tables (hundreds of GB), consider using pt-online-schema-change or gh-ost instead, which rebuild the table without blocking writes.

Common Misconceptions and Mistakes

Many users expect OPTIMIZE TABLE to be a magic speed-up. Here are the most common misunderstandings:

• “It will make all queries faster.â€� – No. If your queries already use indexes efficiently, a rebuild may have negligible impact. It mainly helps with full scans and index range scans on fragmented tables.
• “It defragments the disk.â€� – It reorganizes MySQL’s internal storage, not the physical disk. That’s a different task (e.g., defrag on Windows).
• “It works on all storage engines.â€� – It’s supported for InnoDB and MyISAM, but for other engines (like MEMORY), it may return an error or do nothing.
• “It’s the same as ANALYZE TABLE.â€� – ANALYZE TABLE only updates statistics; it doesn’t rebuild data or indexes. OPTIMIZE TABLE also updates statistics, but does much more.
• “It’s safe to run on a replica.â€� – It is, but it will also run on the source if you’re using statement-based replication. Be aware of the load.

OPTIMIZE TABLE vs. ALTER TABLE vs. ANALYZE TABLE

These three commands are often confused. Here’s a clear comparison:

Command	What it does	When to use
OPTIMIZE TABLE	Rebuilds data and indexes, reclaims space, updates stats	After mass deletes/updates, high fragmentation
ALTER TABLE ... FORCE	Forces a table rebuild (same as OPTIMIZE for InnoDB)	When you want to change engine or row format
ANALYZE TABLE	Updates index statistics only	When query plans are suboptimal due to stale stats

In practice, for InnoDB tables, OPTIMIZE TABLE and ALTER TABLE ... FORCE are nearly identical. The advantage of OPTIMIZE is that it’s simpler and also works for MyISAM.

Costs and Risks: What to Watch For

Optimizing a table is not free. The main costs are:

• Locking – Even with LOCK=NONE, InnoDB needs exclusive locks during the final table swap stage. Concurrent writes may be blocked for a short time.
• Disk I/O and CPU – The rebuild reads and writes the entire table. On a busy server, this can saturate disk and affect other queries.
• Disk space – You need enough free space for the new copy. If you run out, the operation fails and the original table remains intact (thanks to atomic DDL in MySQL 8.0).
• Time – For a 100 GB table, this can take minutes to hours, depending on hardware and indexes.

To mitigate risks, test on a staging server first, and always have a recent backup. In MySQL 8.0, the operation is atomic – if it fails, the table is unchanged.

Alternatives to OPTIMIZE TABLE

If you need to reclaim space or improve performance without a full rebuild, consider these options:

• Use ALTER TABLE ... ENGINE=InnoDB – This also rebuilds the table (similar to OPTIMIZE) but allows you to change other options in the same statement.
• Use online schema change tools – pt-online-schema-change or gh-ost rebuild the table with minimal locking, ideal for large tables in production.
• Partition the table – If fragmentation is a recurring issue, partitioning by date or key can help manage data growth.
• Change the row format – For InnoDB, using COMPRESSED row format can reduce disk usage, but at the cost of CPU.
• Just delete and reinsert – For very small tables, TRUNCATE and reload may be faster, but be careful with foreign keys.

Choose the method based on table size, downtime tolerance, and whether you need to change other table properties.

Frequently Asked Questions

Does OPTIMIZE TABLE lock the table?

Yes, it acquires locks. For InnoDB, the default is an exclusive lock for the duration of the rebuild, but you can specify LOCK=NONE to allow concurrent reads and writes during most of the operation. However, there is still a brief exclusive lock at the end when the table is swapped. MyISAM tables are locked entirely during the process.

How long does OPTIMIZE TABLE take?

It depends on table size, index count, disk speed, and server load. A small table (under 1 GB) might take seconds; a 100 GB table with many indexes can take hours. You can monitor progress via SHOW PROCESSLIST or performance_schema in MySQL 8.0.

Can I run OPTIMIZE TABLE on a running production database?

Yes, but carefully. Use LOCK=NONE if possible, schedule during low traffic, and ensure you have enough disk space. For very large tables, consider using an online schema change tool to avoid blocking writes.

Why didn’t my table size shrink after OPTIMIZE TABLE?

InnoDB tables may not shrink if the innodb_file_per_table setting is off (the data lives in the shared tablespace). Also, if you have a high watermark (e.g., auto-increment values), the table may not shrink as much as expected. Check DATA_FREE before and after to see if fragmentation actually decreased.

Is OPTIMIZE TABLE the same as defragmenting a disk?

No. It reorganizes MySQL’s internal storage, not the filesystem. It packs rows and index pages more tightly, but the physical file placement on disk is handled by the OS.

What happens if OPTIMIZE TABLE fails mid-way?

In MySQL 8.0, the operation is atomic – if it fails, the original table is left unchanged. In earlier versions, you might end up with a corrupted table, so always backup before running it.

Related

• Why We Replaced Hourly Retainers with Four Clear Engineering Rules
• Markup Group Photo Privacy: What You Need to Know
• ESP32 Plant Avatar: Houseplant Health Visualized