Database Repair and Optimization in WordPress (WP_ALLOW_REPAIR)

Different things can happen to database tables and there are many cases where repairing or optimizing database tables solves the problem.

A few examples:

  1. I've had it happen that when I tried to get any data from the database, it just wasn't there, even though when I checked it was there.

  2. Another example, from my colleague's practice - table wp_options weighs a cosmic 11-17 GB, but when dumped only 10-100 MB. After running it:

    OPTIMIZE TABLE wp_options;

    17 GB turned into 311 KB. I did that for all the sites and ended up freeing up about 100 GB of space on the server.

    It must be some kind of cache, which was transferred when copying the database from one site to another.

WordPress Script to Optimize the Database

WordPress has a native script for repairing database tables. It is located in the file wp-admin/maint/repair.php. You can refer to the address: http://site.com/wp-admin/maint/repair.php is a self-sufficient script, but to refer to this file directly by URL, in wp-config.php must be set a constant:

const WP_ALLOW_REPAIR = true;

// or
define( 'WP_ALLOW_REPAIR', true );

If there is such a constant, you will see such a page (dialog box) when you go to the URL:

The difference between the two is that in the second alternative an optimization will be performed after the repair.

The result will be displayed as follows:

See file wp-admin/maint/repair.php

Some words about OPTIMIZE and REPAIR

OPTIMIZE TABLE

This operation reorganizes (defragments) the physical space occupied by the table and indexes, which provides space reduction and acceleration of I/O operations. Specific actions differ for different engines (MyISAM, ARCHIVE and InnoDB are supported).

How do I automate this operation on the server?

For a single table, you can do it with a SQL query:

OPTIMIZE TABLE table_name

For all tables in the database, using the mysqlcheck utility:

mysqlcheck --user=USER --password=PASS --optimize database_name

Or you can use WP CLI command wp db optimize:

$ wp db optimize
Success: Database optimized.

REPAIR TABLE

Recovers a corrupted database table. Works only for some SQL engines, in particular these tables: MyISAM, ARCHIVE and CSV.

Or you can use the WP CLI command wp db repair:

$ wp db repair
Success: Database repaired.

Delete configuration after use

The database needs to be optimized or repaired and this should be done by administrators, so it is recommended to remove WP_ALLOW_REPAIR constant from wp-config.php file after using this feature.

Otherwise, anyone can perform these expensive operations on your database and jeopardize the performance and operation of your site.