Ultimate Guide to Checking, Repairing, and Optimizing MySQL/MariaDB Databases for Peak Performance

MySQL and MariaDB databases need a little care: an occasional check for damaged tables, a repair when something breaks, and sensible settings so queries stay fast. This guide covers how to check, repair and optimise tables safely, how InnoDB crash recovery works, and which tuning settings still matter in 2026. It has two paths: one for shared hosting through phpMyAdmin, and one for your own VPS or server with full command-line access.

1. MyISAM and InnoDB: why it matters

To see which engine each table uses, run:

SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = 'your_database';

If you still have MyISAM tables that crash repeatedly, convert them to InnoDB once they are repaired: ALTER TABLE your_table ENGINE=InnoDB;. That is the most effective way to prevent repeat corruption.

2. On shared hosting: check and repair in the control panel

On shared hosting you don't have root access to the database server, so the command-line and configuration steps later in this guide don't apply. You can still check, repair and optimise your own tables.

If a repair fails, or the table crashes again, open a support ticket with the database and table names. For connection errors, "Too many connections" and import problems, see Database troubleshooting.

3. Check tables on your own server

With shell access to your own VPS or server, you can check tables in SQL:

CHECK TABLE your_database.your_table;

Or check whole databases with mysqlcheck. On MariaDB the same tool is also called mariadb-check.

mysqlcheck --check your_database
mysqlcheck --check --all-databases

Keep the password off the command line, where it shows in the process list; use an option file readable only by you:

# ~/.my.cnf  (chmod 600)
[client]
user=maint_user
password=use-a-long-random-password

mysqlcheck --check locks each table while it reads it. Run it outside busy hours on a live site.

4. Repair MyISAM tables

For a MyISAM table marked as crashed:

REPAIR TABLE your_database.your_table;

or for a whole database:

mysqlcheck --repair your_database

Treat REPAIR TABLE ... USE_FRM as a last resort only: it can lose data.

Before a repair, free disk space if the disk is full. A full disk during a write is a common cause of crashed tables, and the repair will fail or the table will crash again.

5. Recover from InnoDB corruption

InnoDB replays its redo log after an ordinary crash and needs no repair. Real corruption shows as the server refusing to start, or crashing when a particular table is read, with InnoDB errors in the error log. Follow this order.

For a table that is readable but damaged, ALTER TABLE your_table ENGINE=InnoDB; rebuilds it from its own rows, which is often enough.

6. Optimise tables and queries

OPTIMIZE TABLE rebuilds a table to reclaim space. On InnoDB it recreates the table and updates index statistics. It is worth running after deleting a large share of a table's rows, not on a schedule. It needs free disk space about the size of the table.

OPTIMIZE TABLE your_database.your_table;
ANALYZE TABLE your_database.your_table;

Bigger gains come from the queries themselves:

• Find the slow ones. Turn on the slow query log (slow_query_log = 1, long_query_time = 1) and review it.
• Read the plan. Put EXPLAIN before a slow SELECT. A full table scan on a large table usually means a missing index.
• Index what you filter and join on. Columns in WHERE, JOIN and ORDER BY clauses are the candidates.
• Drop unused indexes, because each one slows down every insert and update.

For WordPress sites, most database slowness comes from plugins and the size of the wp_options autoload data. See My website is slow.

7. Server settings that still matter

These settings apply to your own server only. Change one at a time, and measure before and after.

[mysqld]
# 50-70% of RAM on a database-only server; less if the
# same server also runs the web server and PHP
innodb_buffer_pool_size = 1G
# 1 = durable; 2 risks losing ~1 s of writes in a crash
innodb_flush_log_at_trx_commit = 1

max_connections = 150
slow_query_log = 1
long_query_time = 1

Leave most other settings at their defaults; modern versions choose sensible values. Some tuning advice is out of date:

• Query cache: removed in MySQL 8 and disabled by default in MariaDB. Don't enable it.
• innodb_thread_concurrency and huge sort or join buffers: leave them alone unless you have measured a problem. Large per-connection buffers waste memory.
• expire_logs_days: replaced in MySQL 8 by binlog_expire_logs_seconds.
• mysqlpump: deprecated and removed in MySQL 8.4. Use mysqldump, or a physical backup tool for large databases.

Check your version with SELECT VERSION(); before copying any setting, and restart the service (systemctl restart mysql or systemctl restart mariadb, depending on your system) to apply changes.

8. Automate backups, not repairs

A nightly job that "checks and repairs everything" hides problems and can lock tables at the wrong moment. Schedule a nightly mysqldump --single-transaction to a compressed file instead, keep several days of copies with at least one off the server, and test a restore now and then. Check tables only when you see an error.

9. Running this on Domain India

• Shared hosting (cPanel, DirectAdmin, Webuzo): use phpMyAdmin and your panel's database tools as in section 2. You cannot change server settings. Port 3306 is closed to outside connections, so for a desktop client use an SSH tunnel. Jailed SSH access is available on every shared hosting plan (cPanel, DirectAdmin, Webuzo). It is off by default; ask support to enable it for your account. Logins use an SSH key. On cPanel, phpMyAdmin imports files up to 50 MB. Weekly JetBackup 5 backups on cPanel and DirectAdmin include your databases and can be restored from the panel.
• VPS: a Domain India VPS is self-managed with root access, so sections 3 to 8 apply in full. You are responsible for the database server's configuration and backups.

Ready to go further? Read Database troubleshooting for common errors, How to connect to the MySQL database for connection details, or compare VPS plans if you need your own database server.