How to Repair / Optimize WordPress Database?

It is always a good practice to repair and optimize the database tables used by WordPress. WordPress provides a built-in functionality for repairing and optimizing the tables, without needing to open PHPMYAdmin or similar database administration tools.

WordPress can automatically look for some common database problems and repair them. Repairing may take a while, depending on the size of the database tables.

WordPress can also attempt to optimize the database. This improves performance in some situations. Repairing and optimizing the database can take a long time and the database will be locked while optimizing.

Follow the step by step procedure given below to optimize / repair WordPress database tables.

In order for the built-in functionality to work, you need to make sure that, a PHP constant is defined in the WordPress configuration file wp-config.php

You need to add the below code to the WordPress wp-config.php file. This file is located in the root folder of the WordPress installation.

define('WP_ALLOW_REPAIR', true);

Login to the wordpress admin dashboard pointing to your browser to http://yourdomain.com/wordpress/wp-login.php This refers to the wordpress login corresponding to your WordPress installation folder.

When you are logged in successfully, go to http://yourdomain.com/wordpress/wp-admin/maint/repair.php

Now you see two options in the screen : ‘Repair Database’ and ‘Repair and Optimize Database’. Click on the one of your choice.

The wordpress function executes database administration queries such as

CHECK TABLE  table_name 
REPAIR TABLE  table_name
ANALYZE TABLE  table_name 
OPTIMIZE TABLE  table_name

This would improve wordpress performance if too many data read-write has happened in the database.

You may remove the “WP_ALLOW_REPAIR” line from the wp-config.php to prevent this page from being used by unauthorized users.

This is method is useful when your database may be so corrupt that you cannot login.