How to Optimize Database in WordPress for Increasing Page Speed

In Speed Optimization another important part is optimize database. In WordPress there creates so many queries, post revisions and comments. So you have a lot of junks in your database. This junks effects on your wordpress website’s speed. So you need to optimize database. In this post I will walk-through with database optimization.

How to Optimize Database in WordPress

Before we proceed to optimization of database. At first take backup of your wordpress website. Now to optimize database in wordpress can be done in two methods. Those are:

• With phpMyAdmin
• With Plugin

Optimize Database in WordPress With phpMyAdmin

There are so many ways you can run SQL queries on your database, if you have cPanel on your server the best and easiest option is phpMyAdmin.

At first login to your cpanel and navigate to Database section. Then phpMyAdmin

When you’re in phpMyAdmin, you’ll see your website’s databases listed on the left side. Click on the one you want to clean up and then click the “SQL” tab.

It means you selected your desired database what you want to optimize. Now we will run some queries on SQL tab. Before we proceed please note that my default table prefix wp_, so make sure you change the prefixes in the SQL commands below match the ones used by your database.

Delete Old Plugin and Post Data

Let’s start with deleting data from plugins you no longer have installed. Or the data you don’t need on your website. The wp_postmeta table also used for your post data, so when you run this query you’re hitting two birds with one stone.

DELETE FROM wp_postmeta WHERE meta_key = 'META-KEY-NAME';

Change the META-KEY-NAME with the value you want to clear.

Delete Post Revisions

When you publish your post after so many revisions it means you don’t need the revisions as well as the same rule for pages. So, when you don’t need these revisions you need to remove those otherwise it is taking space and slowing down your website. If you want to delete all of the post or revisions in your database, run this query:

DELETE a,b,c
 FROM wp_posts a
 LEFT JOIN wp_term_relationships b ON ( a.ID = b.object_id)
 LEFT JOIN wp_postmeta c ON ( a.ID = c.post_id )
 LEFT JOIN wp_term_taxonomy d ON ( b.term_taxonomy_id = d.term_taxonomy_id)
 WHERE a.post_type = 'revision'
 AND d.taxonomy != 'link_category';

This removes all revisions without unintended data loss and accidentally deleting link relationships.

Delete Spam Comments

It is a problem from wordpress dashboard removing spam comments one by one. So you also can remove spam comments from database SQL query:

DELETE FROM wp_comments WHERE comment_approved = 'spam';

Delete Unapproved Comments

You also can remove unapproved comments through SQL query. That is:

DELETE from wp_comments WHERE comment_approved = '0';

Delete Unused Tags

This query will delete all tags that aren’t associated with any posts:

DELETE FROM wp_terms WHERE term_id IN (SELECT term_id FROM wp_term_taxonomy WHERE count = 0 );
DELETE FROM wp_term_taxonomy WHERE term_id not IN (SELECT term_id FROM wp_terms);
DELETE FROM wp_term_relationships WHERE term_taxonomy_id not IN (SELECT term_taxonomy_id FROM wp_term_taxonomy);

Delete Old Shortcodes

As Old Plugin data, as old revisions you may also don’t need deprecated shortcodes. So to delete Old Shortcodes run this query:

UPDATE wp_post SET post_content = replace(post_content, '[YOUR-SHORTCODE]', '' ) ;

Make sure you chage ‘YOUR-SHORTCODE’ with your desired value.

Delete Pingbacks and Trackbacks

Now-a-days most of the people doesn’t use pingbacks and tracebacks. Though if you use then run this query to delete pingbacks and trackbacks:

DELETE FROM wp_comments WHERE comment_type = 'pingback';
DELETE FROM wp_comments WHERE comment_type = 'trackback';

Delete Transients

Transients is a system what temporarily store cached data in the database and a timeframe after which it will expire and be deleted. Sometimes some plugins transients took a lot of space so you need to remove them some time. To delete transients run this query:

DELETE FROM wp_options WHERE option_name LIKE ('%\_transient\_%');

Optimizing Tables

When you already logged in to phpMyAdmin then you also need to optimize tables. In phpMyAdmin, on the “Structure” tab and then click on the database you want to optimize. Then at the bottom of the list click “Check all”. In the dropbox box beside this option, select “Optimize table”.

phpMyAdmin will automatically optimize your tables.

Optimize Database In WordPress with Plugins

There are lots of plugins to optimize database in wordpress. But here I am showing you best two plugins. what is installed most of the website. Those are:

• WP-Optimize
• WP-Sweep

Wp-Optimize:

With more than 600,000 active installs, WP-Optimize is the most popular database optimization plugin for WordPress. It’s super easy to use, simply click “Run optimization” next to the clean up options you want to run.

 

WP-Sweep:

When you install the plugin, go to “Tools > Sweep” to access the settings. The plugin has an intuitive interface that displays a report of how much unnecessary data is in your database. It’s divided into different sections for posts, comments, users, terms, options and optimizing tables.

When you click “Sweep” beside an entry, the plugin gets to work optimizing your database for that entry. If you want to sweep your whole database, just click “Sweep All” at the bottom of the page.

 

Wrapping Up !

I hope this article helps you optimize and speed up the performance of your database and WordPress site. If you need more speed optimization post check our blog.