How to Repair & Optimise the WordPress Database

WordPress is developed with the scripting language PHP and uses either MySQL or MariaDB as its open-source relational database management system.

Behind the scenes, the WordPress database stores content such as posts and pages, user data, blog post comments, website configuration settings, theme and plugin settings, and more. Unfortunately, the database can be corrupted and when that happens, your website may load slowly or be completely offline.

In this article, I will show you how to repair the WordPress database and optimise it so that your website runs more efficiently.

Why repair the WordPress database?

WordPress websites may slow down or not load correctly for many reasons. Debugging can help detect the cause of problems such as corrupted WordPress files, plugin and theme incompatibility issues, and PHP memory limits being exceeded.

The WordPress debug command WP_DEBUG can be used to show PHP errors and the SAVEQUERIES command can be used to analyse database issues. To use these features, all you have to do is add the code below to your website’s wp-config.php file. If you prefer, you can utilise debugging WordPress plugins such as Query Monitor and WP Debugging to debug your website.

define( 'WP_DEBUG', true );
define( 'SAVEQUERIES', true );

A common error during the WordPress installation process is “Error establishing database connection“. The most likely reason a new website owner will see this error is that the database connection details in the WordPress wp-config.php file are incorrect.

It can also arise if important WordPress files have been corrupted or if your website host is having issues with the MySQL server or MariaDB server. For example, a small hosting package may not be set up to handle a large spike in traffic as that greatly increases the number of database requests being processed. As a result of this, many visitors will see a database connection error message.

Error establishing a database connection
The connection error you may see if your database connection details are incorrect.

A corrupted WordPress database can also slow down your website greatly or put it offline completely.

Database tables can become corrupted because of a number of reasons:

  • Incomplete Writes – Power cuts, server crashes and unplanned server restarts can cause problems if the database is in the process of writing to a database table
  • Hardware Failures – The servers that databases are stored on can experience hardware failures and other technical problems
  • Software Bugs & Version Incompatibility – Software bugs can arise in MySQL and MariaDB databases
  • Corrupted DataMalware, viruses and poorly written code can all affect database data

Thankfully, MySQL and MariaDB both allow you to repair the WordPress database tables that have become corrupted. The CHECK TABLE command will check if a table is corrupted, whilst the REPAIR TABLE command will repair a table if it is corrupted.

  • MySQL’s REPAIR TABLE command supports three different storage engine table types: MyISAMARCHIVE, and CSV. The InnoDB storage engine has built-in repair functionality that executes during restarts, however, manual repairs are also possible.
  • MariaDB’s REPAIR TABLE command supports four different storage engine table types: MyISAM, Archive, CSV and Aria (Aria was developed as a crash-safe replacement for MyISAM). Alternative recovery modes are available for InnoDB databases.

It is worth noting that MyISAM was previously the default storage type of MySQL, though InnoDB is now the default storage type for both MySQL and MariaDB. The InnoDB storage engine is viewed as self-repairing and self-optimising, so manual database repairs are rarely needed. Check out the Wikipedia page for different storage engine types to learn more.

Another command to be aware of is OPTIMIZE TABLE which is supported by MySQL and MariaDB databases. It can be used to reorganise the physical storage of database data, which can reduce the required storage space and improve I/O efficiency. In practice, optimising the WordPress database can lead to faster page loading times.

  • MySQL’s OPTIMIZE TABLE command supports InnoDB, MyISAM and ARCHIVE tables
  • MariaDB’s OPTIMIZE TABLE command supports InnoDB, MyISAM, ARCHIVE and Aria tables

Database optimisation is supported by the following:

  • WordPress Repair Tool – Click the “Repair and Optimise Database” button
  • phpMyAdmin – Click “Check all” and then select “Optimize table” from the dropdown menu
  • WP CLI – Run the command wp db optimize
  • WordPress Plugins – Optimisation is supported by database plugins such as WP-DBManager, Advanced Database Cleaner and WP-Optimize

I recommend optimising your database tables after repairing your tables or performing a database cleanup.

How to Repair the WordPress Database

In this section, I will show you five methods for fixing the WordPress database. Before using any of these methods to repair your website database, I encourage you to:

Be sure to speak to your website hosting company if you are unsure of any of the above steps.

1. Use the WordPress Database Repair Tool

WordPress offers support for automatic database repairs. To enable this feature, all you need to do is add the code below to your wp-config.php file.

define( 'WP_ALLOW_REPAIR', true );

Once enabled, the repair tool can be accessed at yoursite.com/wp-admin/maint/repair.php. The tool gives you the option of either repairing the database or repairing and optimising the database (more on optimisation later).

The WordPress Repair Tool
The WordPress repair tool.

The repair tool will attempt to repair every table in your website database. For a healthy database, you should see a message that each table is fine. Otherwise, you will see messages that say that a particular table has been repaired.

WordPres Database Repaired
An example of the WordPress database repair tool.

As you can see from the screenshot above, WordPress recommends removing the WP_ALLOW_REPAIR command from your wp-config.php file after you have used the tool in order to prevent unauthorised parties from accessing the repair tool.

2. Repair the WordPress Database Using PHPMyAdmin

phpMyAdmin is a free database administration tool for MySQL and MariaDB databases that is supported by most website hosting companies. Other database administration tools work in a similar way.

You should see a link to phpMyAdmin in your web hosting control panel. When you load phpMyAdmin, you will see the database selection menu on the left-hand side of the page.

Selecting the Database
The main dashboard of phpMyAdmin.

Clicking on a database will take you to the structure screen where all tables are listed. Be sure to select the database that is listed in your website’s wp-config.php file.

Database Structure
Viewing the structure of a database.

Click on the “Check all” checkbox which is located underneath the list of database tables. Then select “Repair table“.

phpMyAdmin Repair Table
Repairing a table in phpMyAdmin.

phpMyAdmin should then advise you that your SQL query has been executed successfully and show a report of all of the tables that were repaired.

Database Tables Repaired
Database tables are repaired.

The above process will work with databases that use storage engines that are supported by the REPAIR TABLE command, such as MyISAM.

In the screenshots below, you can see that the WordPress database uses the InnoDB storage engine.

phpMyAdmin
phpMyAdmin lists all of your database tables.
Select to Repair Tables
The repair table menu option.

InnoDB does not support the REPAIR TABLE command. So instead of seeing a message saying that the tables have been repaired, I see the message “The storage engine for the table doesn’t support repair“.

Repair Not Supported
MySQL and MariaDB do not support InnoDB table repairs, but it’s not something to be concerned about as InnoDB self-repairs.

I encourage all WordPress users to familiarise themselves with phpMyAdmin as it’s such an important tool for troubleshooting WordPress problems. It is a great tool for analysing, repairing and optimising databases too.

3. Use the wp db repair Command With WP CLI

WP-CLI is a free command line tool for WordPress that lets you administrate WordPress websites from a terminal. It allows you to update the WordPress core, plugins, themes, and more.

It can be used to optimise the WordPress database with one simple command.

wp db repair

Once you have executed the command on your terminal, you should see the response “Database repaired“.

4. Repair the WordPress Database Through Your Web Hosting Control Panel

Web hosting control panels allow you to create new databases and access database administration tools such as phpMyAdmin. They also provide tools to check and repair databases.

In cPanel, you will see this option under “MySQL Databases“. In Plesk, you will see an option to “Check and Repair” on the main databases page.

Checking and Repairing a Database in cPanel
Checking and repairing a database in cPanel.
Checking and Repairing a Database in Plesk
Checking and repairing a database in Plesk.

5. Repair the WordPress Database Using a WordPress Plugin

As long as you are able to access your website’s WordPress admin area, you can take advantage of the many WordPress database plugins that are available.

One of the best solutions to check out is WP-DBManager. The plugin lets you back up your website database, repair tables and optimise tables. SQL queries can also be executed directly using the plugin.

WP-DBManager
Repairing tables in WP-DBManager.

Advanced Database Cleaner is a fantastic database optimisation tool too. It allows you to repair and optimise database tables, execute cron jobs and clean up WordPress.

Advanced Database Cleaner
Advanced Database Cleaner provides many database optimisation tools.

Be sure to check out other database optimisation plugins that are available in the official WordPress plugin directory.

Final Thoughts

A healthy database ensures your WordPress website stays online and loads quickly. As such, I highly recommend checking, repairing and optimising your database on a regular basis.

WordPress plugins have simplified this task, but if the WordPress admin area is inaccessible, you can fall back on alternative methods such as phpMyAdmin and the WordPress Repair Tool to repair the WordPress database.

Thanks for reading.

Kevin

Frequently Asked Questions

What Caused My WordPress Database to Become Corrupted?

Database tables can become corrupted when the server is interrupted in the middle of writing to tables. This can arise due to hardware failures, unexpected server restarts, software bugs, and more.

Can Database Corruption Be Prevented?

Whilst a reliable hosting setup can reduce the odds of database corruption occurring, it is not something you can prevent in practice as it can occur under many different circumstances.

How Do I Find Out Which Storage Engine My Database Uses?

In the past, WordPress websites typically used the MyISAM storage engine, but newer WordPress installations are more likely to use InnoDB.

You should be able able to check the storage engine type your website uses in the database section of your web hosting control panel. If it is not detailed there, you can view the type in phpMyAdmin.

Do InnoDB Databases Need to Be Repaired?

InnoDB has recovery features that automatically repair and rebuild corrupted database tables, but hardware failures and other serious instances may place you in a position where a manual repair is required.

What’s the Difference Between MySQL & MariaDB?

MySQL is a free relational database management system that was launched in 1995. When MySQL was purchased by Oracle in 2009, many of the original developers of MySQL forked MySQL to create MariaDB.

Both solutions have many of the same features and have great compatibility with each other too. There is, of course, a risk of the projects diverging in the future as new features are released.

kevinmuldoon,

Kevin Muldoon is a professional blogger with a love of travel. He writes regularly about internet marketing on his personal blog and actively talks about technology on YouTube.

Copy link
Powered by Social Snap