How to optimize a MySQL database with SSH

What is optimizing?

A database is where your site keeps its data. When you add and delete lots of data, empty gaps appear inside the tables. Think of a bookshelf with holes where books were taken out. Optimizing packs the data tight again. It can free some disk space and speed up searches.

SSH is a safe way to type commands on a remote server. Ask Hostvento support whether SSH is on for your plan.

Warning: Optimizing locks a table while it works. Your site may pause for a short time. Take a backup first, and run it when traffic is low.

Optimize all databases

1. Log in to your server with SSH.
2. Run:

   mysqlcheck -u username -p --optimize --all-databases

   This checks every table in every database you can access and optimizes it. Use an admin user on a VPS. It asks for the password.
3. Wait for the list. Each table should show OK.

Optimize one database

mysqlcheck -u username -p --optimize dbname

This optimizes only the database named dbname.

Optimize one table

1. Open the MySQL prompt:

   mysql -u username -p

2. Choose your database:

   USE dbname;

3. Optimize the table:

   OPTIMIZE TABLE tablename;

   This tidies that one table.
4. Leave with EXIT;

A word on InnoDB

Most tables use a type called InnoDB. For these, MySQL may show "Table does not support optimize, doing recreate + analyze instead". This message is normal. The table is rebuilt, and it still works.

Check and repair too

mysqlcheck -u username -p --check dbname
mysqlcheck -u username -p --auto-repair dbname

The first command looks for errors. The second tries to fix damaged tables.

Need help? Open a support ticket.

Quick recap

• Optimizing removes gaps in tables.
• Use mysqlcheck --optimize for one or all databases.
• Use OPTIMIZE TABLE for a single table.
• Back up first and run it at quiet times.