Knowledge Base
MilesWeb / Database

How to Optimize a MySQL Database Using SSH?

Approx. read time : 2 min

By optimizing your MySQL database tables you can make improvements to your website. The optimizing function helps in reorganizing the table and index data, reducing space and improving I/O efficiency.

In this guide, you will learn to optimize your tables via SSH.

Steps to Optimize Your MySQL Database Using SSH

Sometimes if you run an optimization via phpMyAdmin, it leads to failure and the page ultimately times out. The reason behind this is the large size of database which the phpMyAdmin can’t handle. So, the alternative method is to optimize via SSH. If you are a shell user, you can SSH into your domain and perform the following command which is the same function run on phpMyAdmin:

In the above command, replace the variables with your actual database information:

  • username – database username
  • password – user’s password
  • hostname – database hostname that you set up and is active
  • databasename – the database’s name in which you’re running the command

You can get the log of the command into a file for viewing by adding the below to the end of the command:

Using a cron job to Optimize

Using a shell script and/or cron job using mysqlcheck, you can also cleanup overhead which checks, repairs, and optimizes tables. It’s best to run a monthly cron job to do this for you. The cron job might look as below:

The items in all-caps need to be replaced with the actual credentials for your database.

Repairing via SSH

A command can be run to repair a table instead of optimizing it:

In this way, you can optimize a MySQL database using SSH.

Pallavi Godse
Pallavi is a Digital Marketing Executive at MilesWeb and has an experience of over 4 years in content development. She is interested in writing engaging content on business, technology, web hosting and other topics related to information technology.