ANALYZE and OPTIMIZE TABLE

ANALYZE TABLE updates index statistics so the optimizer makes better choices. OPTIMIZE TABLE defragments the table and reclaims space.

Example

-- Update optimizer statistics
ANALYZE TABLE orders;

-- Defragment and reclaim space (rebuilds table)
OPTIMIZE TABLE orders;

-- Check fragmentation
SELECT TABLE_NAME,
       ROUND(DATA_FREE/1024/1024, 2) AS free_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY DATA_FREE DESC;

SQL

Full Editor

Pro Tip

OPTIMIZE TABLE locks the table on non-partitioned InnoDB — schedule during low traffic.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned