Optimize WordPress Database by PhpMyAdmin

Comments Off on Optimize WordPress Database by PhpMyAdmin

Optimize WordPress Database by PhpMyAdmin

This post will describe how to “Optimize WordPress Database by PhpMyAdmin” in step by step. Now a days, many people even not coming from technical background create and maintain blog website on WordPress. There are many Plugins available for this but thumb role is avoid plugin if possible to do it directly. Moreover, you  know what you are doing whereas for plugin you have to completely unaware of plugin internal code. Some plugins may destroy your database. This  article will help them who want to  keep their database fast and optimized.

As time goes on, there are some unnecessary data stored or should be removed in/from WordPress database. So at first delete this unnecessary data from your database by the following procedure

Step 1:   Log in to your cPanel and select phpMyadmincPanelPHPMyAdmin

Step 2:  After logged in to phpMyAdmin, select the database from the right corner

Step 3:  Click SQL tab at the Top


Step 4: Now copy the following SQL in the  SQL  box and click Go button.

delete from wp_comments where comment_post_ID not in ( select ID from wp_posts);
delete from wp_commentmeta where comment_id not in ( select comment_id from wp_comments);
delete from wp_postmeta where post_id not in (select id from wp_posts);
delete from wp_term_relationships where object_id not in ( select id from wp_posts);
delete from wp_term_relationships where term_taxonomy_id not in ( select term_taxonomy_id from wp_term_taxonomy);
delete from wp_usermeta where user_id not in ( select id from wp_users);
optimize table wp_comments;
optimize table wp_commentmeta;
optimize table wp_postmeta;
optimize table wp_term_relationships;
optimize table wp_users;



You can run the operation once a month that will  delete unnecessary data from your Database and thereafter optimize related tables. Now you should optimize the whole database.   If you don’t want to delete any data but would like to optimize whole database only ,  follow the following steps.

  1. Select you Database through Step 1 and Step 2 mentioned above.
  2. Check all tables
  3. Just right of check all select optimize tablesPHPMyAdminOptimizeTable
  4. Check the output after clicking optimize table
  5. Optimize all tables by a single command
    If you have command prompt OS access in Linux/Unix you can do it by a single command.

     [root@localhost ~]# mysqlcheck -o wp_test -u test -p

Optimization requires at least once a month. There is a common theory in Database “If you have fewer rows in a table , you have quicker access”. As WordPress wp_posts and wp_contents table may contain huge rows and you may need to delete some old posts and comment to speed up your website. After deletion the index need to be rebuild to take the advantage of reducing tables rows. Optimize command actually do this.

There is a same procedure by which you can Repair WordPress Database by PhpMyAdmin