Repair and optimize WordPress database

It is always good practice to optimize your blog's database to improve blog's performance. Many are not aware about WordPress's built-in database repair and optimize functionality, as it is not available in admin back-end. You can check this by visiting http://yourdomain.com/wp-admin/maint/repair.php (replace http://yourdomain.com/ with your actual blog/site url ). Here is a step by step guide one how to go about repair and optimizing WordPress database.

It is always good practice to optimize your blog’s database to improve blog’s performance. Many are not aware about WordPress’s built-in database repair and optimize functionality, as it is not available in admin back-end.

You can check this by visiting http://yourdomain.com/wp-admin/maint/repair.php (replace http://yourdomain.com/ with your actual blog/site url ).

Below is a step by step guide on how to optimize WordPress database :

  1. First login to admin section of WordPress blog by visiting http://yourdomain.com/wp-login.php .
  2. Then go to http://yourdomain.com/wp-admin/maint/repair.php .You can see screen as show below. It ask you to add a line of code to WordPress’s wp-config.php file.
  3. Add below code to your WordPress wp-config.php file and reload/refresh page.
  4. define('WP_ALLOW_REPAIR', true);


  5. After refreshing you can see page with options to ‘Repair Database’ and to ‘Repair and Optimize Database’. Just click on one of the option.
  6. If you click on ‘Repair Database’ button,  WordPress can automatically look for some common database problems and repair them. Internally it executes two sql queries on each WordPress database tables in loop and these queries are CHECK TABLE  table_name and REPAIR TABLE  table_name .
  7. If you click on ‘Repair and Optimize Database’ button, 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. Internally it executes four sql queries along with above two queries on each WordPress database tables in loop and other two queries are ANALYZE TABLE  table_name and OPTIMIZE TABLE  table_name .

Hope this will improve your WordPress blog’s performance. Drop in your comments!

(P.S. : Please take backup of your WordPress database before making any action on database)

7 Comments

Renji August 24, 2011

Do I need to remove define('WP_ALLOW_REPAIR', true); after optimizing?

santosh August 28, 2011

@Renji its not necessary to keep define(‘WP_ALLOW_REPAIR’, true); you can remove it once you done with optimization.

Raj September 7, 2011

which is the best way to optimize wordpress, phpmyadmin or using your tutorial??

santosh September 7, 2011

both are options and not the optimization utility. using both option you can perform the same operation, as mentioned in post
1. CHECK TABLE
2. REPAIR TABLE
3. ANALYZE TABLE
4. OPTIMIZE TABLE

Lucas February 10, 2013

Thanks! i did it but there is still something wrong on my database… subscribers can not see their profile, they get an error message telling that they don’t have enough privileges to see the page… any idea of what maybe the problem?

Brendan May 3, 2013

Thanks for showing me how to do this. Valuable. Im surprise WP admin doesn’t have a button to do this. I never knew it existed.

Cheers
Brendan