Overview
This article describes how to optimize your database via phpMyAdmin or SSH, helping to improve the efficiency of your website.
Optimizing via phpMyAdmin
The following steps use phpMyAdmin to optimize and repair tables in your database.
Log in to your database
See this article for instructions on how to log into your database via phpMyAdmin.
Check the database size
Before optimizing your database, you should check the current size. In the left menu, click the database name.
To the right of the bottom row, you will see the full size of the database.
Optimize or repair tables
You have the option to optimize or repair the database tables. These are similar but used under different circumstances:
- Optimize Table - Reorganizes the physical storage for table data and associated indexes. This function improves table access by reducing storage space.
- Repair Table - Repairs a possibly corrupted table. This is run if you're getting database errors.
To optimize or repair:
- In the left menu, select your database to view all tables.
- To the right, view the Overhead column.
- If you see any numerical values, those tables can be optimized. You can also click the Check All link on the bottom left, which selects all tables.
- At the bottom of your table list, click the With selected dropdown menu.
- From the dropdown menu, select Optimize table or Repair option.
The optimization may take time to complete depending on the size of the database. 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.
Optimizing via SSH
The following uses SSH to optimize or repair tables in your database. This is helpful in larger databases if you notice phpMyAdmin timing out.
Check the database size
Connect to a database via SSH and run the following query to view 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;
Optimize tables
Log in to your server and run the mysqlcheck command to check, repair, and optimize tables. Make sure to replace the variables in the command with your database credentials.
[server]$ mysqlcheck -h HOSTNAME -u USERNAME -p -o DATABASE
You may see the following note in the command output:
Table does not support optimize, doing recreate + analyze insteadThis message occurs when MySQL's InnoDB storage engine performs a different set of operations to optimize the table and can be safely disregarded.
View log data
-
You can output the log data into a file by adding the following to the end of the command:
>> results.txt
Then use the cat command to read the file:
cat results.txt
Use a cron job to optimize
-
Another option is to create a cron job to optimize using the mysqlcheck command.
- Create a file named something like optimize-db.sh in your user's home directory.
- Add this code to it (using your database credentials).
#!/bin/sh /usr/bin/mysqlcheck -o -v -u USERNAME -pPASSWORD -h MYSQL.EXAMPLE.COM DATABASENAME;
- Create a cron job to run this file. The cron command may look like this:
/bin/sh /home/username/optimize-db.sh
A good practice is to run this on a monthly schedule.
Repair a table
If you're seeing database errors, run this command to repair a corrupted 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