How do I optimize my database?

Overview

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.

Accessing your database

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

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

Checking your database size

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

Using the instructions below, you can view your database's size within phpMyAdmin.

  1. Click the database name you're checking the size of on the left-side menu.
    phpMyAdmin database size
  2. On the far-right side of the bottom row of the main panel, 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:

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;
      

Optimizing your database

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:
    01 Optimizing Database.fw.png
  2. Look at the Overhead column to the far 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.
    02 Optimizing Database.fw.png
  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:
    03 Optimizing Database.fw.png

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

Repairing tables

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.

01 Optimizing Database repair table.fw.png

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.

Optimizing via SSH

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 may see the following note in the command output:

Table does not support optimize, doing recreate + analyze instead

This message occurs when MySQL's InnoDB storage engine performs a different set of operations to optimize the table and can be safely disregarded.

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 cleanup 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 USER -p PASSWORD -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 

See also

Did this article answer your questions?

Article last updated PST.

Still not finding what you're looking for?