Optimsing SQL Tables
If you recently:
inserted a lot of rows,
deleted many rows,
table sluggish to load
or if MySQL hasn’t updated statistics for a while,
… then the cardinality values may look wrong.
ANALYZE TABLE your_table;
What happens internally:
When you run ANALYZE TABLE:
MySQL scans the index pages.
It samples index values.
It recomputes:
cardinality estimates,
distribution of values,
index selectivity.
It stores the updated statistics.
This helps the optimizer choose the best index for your queries.
It's safe to use and for MyISAM: It briefly locks the table.
Worst case: statistics don’t improve.
Works with:
SHOW INDEX FROM your_table;
OPTIMIZE TABLE is helpful when:
You’ve deleted or updated millions of rows
Your table has become fragmented
Index cardinality looks wrong even after ANALYZE TABLE
Query performance has dropped
.MYI file has grown too large
Table is heavily fragmented
You want to reclaim disk space
Queries have become slower over time
OPTIMIZE TABLE your_table;
This will:
temporarily locks the table, Read AND write access is blocked. The table is not usable until the process is complete. Large tables = longer downtime.
rebuild the table,
recreate indexes,
update statistics,
and remove fragmentation.
This reduces file size and can speed up table scans.
Think of it as a “full refresh” for the table structure.
REPAIR TABLE your_table;
Repairs a corrupted table.
It attempts to fix:
•damaged .MYD data
•damaged .MYI indexes
When to use it:
•MyISAM table shows errors
•Queries fail with “table corrupted”
•Server crashed during write
•Index file is broken
SHOW INDEX FROM your_table;
ALTER TABLE your_table DROP INDEX index_name,
ADD INDEX index_name (column_name);
Topic last reviewed: 26/11/25