Optimize a database

Optimizing your MySQL database tables is one way of making improvements to your website. The optimizing function reorganizes the table and index data, which reduces space and improves I/O efficiency.

This guide describes how to optimize your tables via phpMyAdmin.

For detailed instructions on how to log into your database via phpMyAdmin, please visit the following article:

• phpMyAdmin overview

Once you’re logged in, proceed with the steps below.

Before optimizing your database, you should check the current size of your database. You can check your database's size using phpMyAdmin or SSH.

Using phpMyAdmin

Click the database name in the left menu.

To the right of the bottom row, you can see the full size of the database.

Using SSH

You can also use SSH to connect and log into your database and follow the steps below to find your database's size. View the following article for instructions on connecting via SSH:

• Connect to a database via SSH

Run the following query to see the size of the database in MB (do not replace any text or add any line breaks):

SELECT table_schema "database", sum(data_length + index_length)/1024/1024 "size in MB" FROM information_schema.TABLES GROUP BY table_schema;

On the left side of phpMyAdmin, you’ll see a list of databases:

1. Select your database from the list.

   In the center panel, a list of your tables appears.

2. View the Overhead column to the right.

   If you see any numerical values, those tables can be optimized. In the example above, only those tables have been selected.

   You can also click the Check All link on the bottom left, which selects all tables.

3. At the bottom of your table list, click the With selected dropdown menu.
4. From the dropdown menu, select Optimize table.

   The optimization may take time to complete depending how large the database is. Once it completes, the page displays the query that was run along with a success message.

   You can safely disregard the “Table does not support optimize, doing recreate + analyze instead” message as it's just informational.

In addition to optimizing a table, you can also 'repair' a table. Follow all steps mentioned above. In step #3, choose Repair table from the dropdown menu.

The two options are similar but used under different circumstances:

• Repair Table - Repairs a possibly corrupted table. This is run generally if you’re getting database errors. This can be run via phpMyAdmin or set up as a cron job to have it run routinely.

• Optimize Table - Simply reorganizes the physical storage for table data and associated indexes. This function improves table access as part of its function is to reduce storage space.

Sometimes running an optimization via phpMyAdmin tends to fail and the page eventually times out. This is possibly due to the database being too large for phpMyAdmin to handle. The alternative is to optimize via SSH. As a shell user, you can SSH into your domain and perform the following command which is the same function run on phpMyAdmin:

[server]$ mysqlcheck -h HOSTNAME -u USERNAME -p -o DATABASE

Replace the variables in the above command with your actual database information:

• USERNAME – database username.
• HOSTNAME – database hostname you set up and is active.
• DATABASE – the name of the database in which you’re running the command.

-p will prompt you to enter your database password, which will prevent the password from being saved in your SSH user's bash history.

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

>> results.txt

 Using a cron job to optimize

You can also clean up overhead via a shell script and/or cron job using mysqlcheck which checks, repairs, and optimizes tables. It's a good idea to run a monthly cron job to do this for you. The cron job might look like this:

#!/bin/sh
/usr/bin/mysqlcheck -o -v -u USERNAME -pPASSWORD -h MYSQL.EXAMPLE.COM DATABASENAME;

Replace the items in all-caps with the actual credentials for your database.

Repairing via SSH

You can also run a command to repair a table instead of optimizing it:

[server]$ mysql -u USERNAME -pPASSWORD -h HOSTNAME DATABASENAME -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "repair table " $1 ";"}' | mysql -u USERNAME -pPASSWORD -h HOSTNAME DATABASENAME