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