Optimising and Repairing MySQL Databases

Over time, MySQL databases can become fragmented or develop errors, particularly after frequent inserts, updates, and deletions. Optimising and repairing your databases keeps them running efficiently.

Please note: Screens and options may vary slightly depending on your cPanel version and hosting plan.

Optimising via phpMyAdmin

1. Open phpMyAdmin in cPanel.
2. Select the database.
3. Click Check All to select all tables.
4. From the With selected dropdown, choose Optimize table.
5. phpMyAdmin will defragment the tables and reclaim unused space.

Repairing via phpMyAdmin

If tables have errors (e.g. after a server crash):

1. Open phpMyAdmin.
2. Select the database.
3. Select the affected tables (or all tables).
4. From the With selected dropdown, choose Repair table.

Checking Table Health

1. In phpMyAdmin, select all tables.
2. From the With selected dropdown, choose Check table.
3. The status will show whether each table is OK or needs repair.

Via SSH (Command Line)

# Check all tables in a database
mysqlcheck -u username_dbuser -p username_database

# Optimise all tables
mysqlcheck -u username_dbuser -p --optimize username_database

# Repair all tables
mysqlcheck -u username_dbuser -p --repair username_database

# Check, optimise, and auto-repair
mysqlcheck -u username_dbuser -p --auto-repair --optimize username_database

When to Optimise

• After deleting a large amount of data.
• After importing large datasets.
• If your website or application feels sluggish and database queries are slow.
• As routine maintenance — monthly optimisation is good practice.

Tips

• Optimising locks tables temporarily. Run optimisation during low-traffic periods.
• For WordPress sites, plugins like WP-Optimize can automate database cleanup and optimisation.
• If repair fails, you may need to restore from a backup. Always maintain recent backups.
• Large InnoDB tables (the default storage engine in modern MySQL) are largely self-optimising, but occasional OPTIMIZE TABLE commands can still help reclaim disk space.

What Next?

• Managing Databases with phpMyAdmin — Full database management.
• Backing Up and Restoring a Database — Ensure you have backups before maintenance.

Related Articles

• Managing Databases with phpMyAdmin
• Backing Up and Restoring a Database
• Understanding CPU and Memory Resource Usage