How do I optimize my 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.

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.

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]$ mysql -u username -ppassword -h hostname databasename -e "show tables" | grep -v Tables_in | grep -v "+" | gawk '{print "optimize table " $1 ";"}' | mysql -u username -ppassword -h hostname databasename 

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

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

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:

/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?