How to optimize WordPress Database to Improve Blog Performance?

How to optimize WordPress Database to Improve Blog Performance?

WordPress database stores all kinds of website’s contents like blog posts, pages, comments and custom post types like links, portfolio items and form entries.WordPress database also stores website settings, plugin settings, and theme settings.The website owner has to update the website regularly which means the database will grow over time. If the database is large then it can affect the performance of your website as it takes much longer for your server to retrieve information from
database tables. That’s why database optimization has become very important. If you remove the unnecessary data, you can improve the efficiency of your database
which will make your web pages to load faster.

Although there are so many WordPress plugins freely available you can take the WordPress plugin development services if you want a custom plugin which has
the robust code and has fast running speed. If plugin code is written cleanly then it is better than freely available plugins as it will load the website faster. Now we will discuss about ways how you can optimize your WordPress database.

How to optimize WordPress Database to Improve Blog Performance?

WordPress Database

Are you using WordPress to publish content on the internet? If yes then you should understand the core WordPress database tables if you are planning to optimize your
WordPress database.WordPress has 11 core tables which can change in the future version of WordPress.Most of the WordPress websites can have much more tables as plugins save settings and other data in the WordPress database. Themes may also save settings and some other data in your WordPress database.

When you check your own database you will find 11 default tables as listed below.Other tables in your WordPress database were created manually or created by WordPress theme or WordPress plugin. If you take WordPress custom plugin development services then make sure they don’t take unnecessary space in WordPress database and then them specifically to optimize the database.

Now let’s see what each WordPress database table stores:-

 

1- wp_commentmeta
It stores meta information about comments.

2- Wp_comments
It stores your comments to the blog posts.
3- wp_links
It stores blogroll link. Blogroll features is deprecated now.
4- Wp_options
It stores the options which are defined in the admin settings area.
5- Wp_postmeta
It stores meta information of posts.
6- Wp_posts
It stores data for posts, pages and other custom post types.
7- Wp_terms
It stores post tags and categories for posts and links.
8- Wp_term_relationships.
It stores the association between posts and categories and tags and also the
association between links and link categories.
9- Wp_term_taxonomy.
It stores a description of the taxonomy (tag, link or category) used in
a wp_terms table.
10- Wp_usermeta
It stores meta information about users.
11- Wp_users
It stores information about users.

 

You can also check the database description page on WordPress.org website for
more information about WordPress core tables.

Optimizing Your WordPress Database

The most common way to manage a WordPress database is by using phpMyAdmin. If you are not using cPanel as your hosting control panel then your hosting plan may be using some different MySQL management tool to phpMyAdmin but most of the database management tools are similar and work in the same way hence you should not worry at all.

You can also manage your WordPress database using a plugin called Adminer which was earlier known as phpMinAdmin. A database WordPress plugin can make accessing your database simpler but it is a security risk because if you have installed a plugin like Adminer and if an unauthorized person gain access to your database then your website will be at risk.

If you check your database then you can see two columns at the end: i.e size and overhead. The size of the table depends on the amount of the data that is stores inside it. If we add more rows in a table then the size of the table increases.While overhead is a temporary disk space which is used by your database to store queries. Over time a table’s overhead will also increase.

It is normal to have overhead in your WordPress database and it doesn’t affect performance unless overhead gets too high.

If you optimize your database then it will remove the overhead and reduce the overall size of your database.

 

You can also optimize tables which are affected by overhead by using the SQL command OPTIMIZE TABLE. For e.g. You can optimize the wp_posts table by executing this SQL query:-

OPTIMIZE TABLE ‘wp_posts’

Although if you are using phpMyAdmin then you don’t need to use an SQL command. phpMyAdmin allows you to optimize tables from the main drop down menu. You can click on the “Check All”box and select “Optimize table” from the menu and then click on the “Go button”.

By optimizing your WordPress database you can remove any accumulated overhead. After you have optimized your WordPress database, phpMyAdmin will confirm that your tables have been optimized.

 

Reducing Size of WordPress Database

 

If you want to speed up your WordPress hosted website then now we are discussing some more points about it. Reducing the size of your WordPress database can significantly reduce your site’s loading time and improve its speed a lot. Search Engines give a lot of importance to the speed of website and your website loading time can affect your search engine rankings. A faster website can boost your ranking as it will assists the crawlers to access your web pages easily and it will also provide a much better user experience to the visitors of your site.

 

If your WordPress database is large then it will take more time to render pages for those visiting your website. Hence it is essential to clean up WordPress database and reduce its size. Your database may have unused records, redundant entries and tables which you can safely remove without affecting other elements of your website. This will help in removing the load time on your server and will also improve the site’s performance.

 

Now we will explain how to reduce the size of your WordPress database.

Delete Unwanted Plugins

There are over 20,000 WordPress plugins. You can install a plugin very easily but it can increase the size of your WordPress database. Your database can suffer from plugin bloat as each plugin, even if we deactivate it adds some code for the WordPress to load. Hence you should delete all those plugins which you haven’t used for a while. This you can do easily through the plugin section in your WordPress dashboard.

Delete Spam Comments and Unwanted Codes

If there are some comments and posts in your span and trash folder then you can remove this immediately as it is unnecessary. You should also delete any post revisions which were left as drafts and new posts were published.

You can use the WP-optimize plugin to do the job with a single click. Also you can use the clean options for WordPress plugin to find the unused tables in your database and clean them also.

 

  • Use MySQL Queries

 

If you use the Akismet plugin then you will find that it has increased the database size in many instances due to the table named wp_commentmeta. You can install the WP db manager plugin and run the following 2 queries. They can be executed from the WordPress dashboard easily but you have to run each of them separately.

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);

                          
DELETE FROM wp_commentmeta WHERE meta_key LIKE ‘%akismet%’;

 

As your WordPress installation might be a bit different hence don’t forget to check your table name before running the queries above. The below queries are also helpful in reducing the size of WordPress database:-

 

 

 

 

 

  • Compressing Images

 

 

If you compress your images then it can reduce immensely the amount of data residing in your WordPress database. You can also use gzip compression to reduce the size of text based resources such as HTML, JavaScript, CSS etc. You can also use the WP Smush.it plugin to compress the images at the time when you upload new ones to the media library by stripping the metadata from JPEGs, stripping the unused colors from indexed images, converting GIFs into indexed PNGs and optimizing JPEG compression. The plugin WP Smush.it can run silently behind the scenes.

 

  • Turn off the Autosave

 

WordPress has a feature by which it saves the changes you make to the posts every 2 minutes and stores them as revisions. All of the revisions of a single post are saved as different entries in the database which makes the database very large. If you are writing long posts then turning off the autosave feature is a good solution. For this you can add the following code to your functions.php file between the opening and closing php tags:-

 

 

 

  • Optimize WordPress database

 

 

In the database everything is stored like the comments, settings, pages, plugins, posts and codes. If the database is bloated then every time a page is reloaded, it will take longer for the information to be found and displayed.

 

Hence you should optimize your database by using some of these plugins:-

 

WP-DB Manager or WP optimize plugin. Using these plugins you can restore, repair, optimize or backup your database easily.

 

 

  • Use Cache Plugins

 

 

Caching plugins like W3 Total Cache can create a static version of your pages for the first time visitors. Hence the database is accessed to generate your page when someone visits the page for the first time. Using cache plugin, the generated page is stored on the server hence when a user visits the page next time the same page is pre-generated and the database is not called in again.


Conclusion

 

As it is very important for the websites to load quickly hence you should implement the above-discussed measures to optimize and reduce the size of your WordPress database. With these measures, your WordPress website will load quickly and you will also get higher ranking in the search engines. This will increase the conversion rate and will boost your business too.

 

Author Bio :

Sophia Phillips is an expert frond-end & WordPress developer. Currently, she is employed with Wordprax Ltd.- a leading PSD to WordPress theme converter company. Sophia has had written a remarkable number of articles on WordPress tricks and tips.

Social Profiles :

https://www.twitter.com/WordPrax/
https://www.facebook.com/wordprax/
https://www.pinterest.com/Wordprax/
https://plus.google.com/u/1/+WordpraxLtd

 

How to optimize WordPress Database to Improve Blog Performance?

Read previous post:
Angular 4.0 the Next Level Front End Technology
What Makes Angular 4.0 the Next Level Front End Technology?

What Makes Angular 4.0 the Next Level Front End Technology? In the modern era of digital commerce and prevalence of...

Close