Like Quertime on Facebook!

15 Practices to Optimize Your WordPress Database

Filed under WordPress

Have you ever felt that your WordPress store’s performance has degraded over time? The web pages are loading really slow, The search box is taking an eternity to generate results, web forms are stuck with the loading icon when users submit the form and eventually your website visitors end up getting frustrated due to the sluggish user experience.

manage-wordpress-database

A sluggish user experience can have a really negative impact on your sales funnels as your leads will not be amused landing on your website and seeing a torpid performance.

Amidst all this, you start wondering whether it was a wise choice to use WordPress for your website! Everything seemed to favor WordPress during the research phase and it emerged as a perfect solution to handle your online presence. But no one told you that things will start deteriorating like falling dominoes and now you have no idea where to begin with to contain the fallout.

Well, there’s good news. These issues can be avoided or resolved with regular maintenance and better management of your WordPress database. To achieve this, we have jotted down an effective approach that ensures your WordPress database remains:

  • Clean
  • Modularized
  • Bug Free
  • Secure

If you follow these tips habitually, then you can leverage the most out of WordPress irrespective of the business you use it for. Ready, let’s begin:

1. Keep deleting unused plugins

uninstall-unused-wordpress-plugins

Plugins in WordPress is quite similar to Applications you install in your iPhone or Android smartphone. They will enhance your website’s functionality in one way or the other. However, as time passes you may end up installing lot of unnecessary plugins and never even use them.

As soon as you install a plugin, it creates it’s own tables to store data in the MySQL database. Hence, to keep your database clean it is a good practice to delete any plugins you don’t really use.

Additionally, always keep in mind that most plugins don’t automatically remove their tables from the database after you delete them. Hence in such instances, always delete these tables manually using phpMyAdmin.

If you are not that comfortable with phpMyAdmin, then try using “Garbage Collector” which is a WordPress plugin that can clean up such unwanted tables. This scans your WordPress database and detects the tables that have been leftover from a deleted plugin.

2. Utilizing phpMyadmin to optimize WordPress database

Database fragmentation is a common sight in a website that has run some miles. This in turn affects the query execution time as size of the data stored in the database gradually increases. To make sure this doesn’t impact the performance drastically, MySQL database comes with an “OPTIMIZE” command which segments the data automatically.

This command can be retrieved following these steps:

  • Go to phpMyAdmin and click on your WordPress Database. (This will show you a list of your WordPress tables.)
  • Click on Check All link below the tables.
  • Next to it there is a “With Selected” drop down, click on it and choose Optimize table.

3. Minimize Post Revisions

minimize-wordpress-post-revisions

Being one of the most effective blogging platform, WordPress tries to impress by saving every revision you make to your post by creating a list of revisions named as “Post Revisions”. These will save every single edit you make infinitely, which after a while can make your site run slower. You might not even need so many revisions to be saved. Hence it is wise to limit the number of revisions per post.

This change can be made in the wp-config.php file and by adding the following code you can limit the number of post revisions:

define( ‘WP_POST_REVISIONS’, n );

Here ‘n’ denotes the number of revisions you want WordPress to save. Ideally 3 to 4 revisions should be enough for a WordPress post. Some users even prefer to switch revisions off by setting the value of ‘n’ to ‘0’ or ‘false’.

4. Prohibit SPAM comments

Comments on your web pages and posts can make or break the impression your visitors have. While good and interactive comments can help engage a site visitor and can even encourage them to join the discussion or leave their review, SPAM comments can have an adverse effect on your visitors as well as your WordPress database.

stop-wordpress-spam-comments

To avoid Spam comments, you either set the comments to be “Admin approved” only or you can limit the number of comments that appear per page.

To configure this:

  • Open your WordPress admin dashboard.
  • Hover on Settings.
  • Click on discussion and check the “Break comments into pages” box.

You can then select the number of comments per page (default is set to 50).

Another alternative is to use a reliable anti-spam plugin like Akismet, which can effectively filter spammers and reduce spam comments.

5. Retrieving deleted items

Did you ever accidentally deleted an item while maneuvering through the WordPress dashboard? It happened to me a lot of times in my early days with WordPress. All hope seemed lost until i discovered that all deleted items are saved into the trash folder once deleted. This allows users to restore items from trash (if they ever need them again).

The deleted items remain in Trash (by default) for 30 days, but did you know WordPress allows you change this duration?

Yes, similar to how we managed Post revisions – We can specify in the wp-config file, exactly after how many days the Trash folder should be emptied.

The query to be used here is:

define( 'EMPTY_TRASH_DAYS', n );

(Where n is the number of days.)

6. Keep deleting unused media

Many a times you end up uploading a wrong file or overtime you update the outdated media files in your website. However, the old files still remains in your database even when not in used. Hence, always remove unused media manually.

Go to Add Media -> Media Library -> Unattached and then delete those files no longer in use.

7. Enable Gzip Compression

enable-gzip-compression

Also known as HTTP or browser compression, Gzip compression decreases the page load speed of your website by compressing the size of files for faster network transfers.

Note that setting up Gzip compression involves editing the sensitive .htaccess file. It’s advisable to ask your hosting provider first to set this up for you.

Once done, go to Checkgzipcompression.com to check if your web server is sending the correct gzip enabled header.

8. Avoid hosting media on your server

Unless you are using a dedicated server from a superior vendor, it is advised to host media files like images, audios and videos on external servers, for eg: Youtube, Soundcloud, Imgur etc. The media files are usually the ones that take longest to load because of their size and storing them in your database means the database is bound to get slow.

If you host these files externally then not only your database will remain light, the media files will also load quickly as external media hosting services have set up dedicated servers to tackle such kind of data and still provide optimal performance.

9. Setup Database Caching

Utilizing plugins like – W3 Total Cache and WP Rocket (which are two of the most reliable caching plugins), and facilitates database caching. These plugins can store the results of queries that are made to the database in cache and whenever similar queries are performed, instead of fetching the data from the database it uses cache to load the results promptly.

This process will make your web page load quickly and be more responsive to user queries.

10. Disable Pingbacks and Trackbacks

wordpress-pingbacks-trackbacks

Pingbacks and Trackbacks are yet another feature that WordPress introduced with a hope to update the blog admin with notifications whenever their blog is mentioned in any other blog. But this feature became virtually useless and eventually led to slow loading speed. Hence, it is advised to turn this off.

To disable pingbacks and trackbacks, simply go to Settings -> Discussion.
Then uncheck “Allow link notifications from other blogs”.

11. Simplify the code

One of the widely recommended trick by Google to keep your website light weight and have quick loading time is by Minifying the HTML and CSS of your code. Minification simply entails removing unnecessary characters and whitespaces from source code without changing the code’s functionality.

For a developer, this could be as easy as a pie, but if you aren’t that technical then you can leverage plugins like – Autoptimize.

It substantially speeds up your website and automatically minify JS, CSS and HTML of your WordPress site.

12. Use the latest WordPress release

This is a no brainer. Keeping your WordPress setup up to date is crucial so you get all the latest security patches, new features, better performance, improved compatibility and several bug fixes.

However, it is still not advised to setup the WordPress upgrade for auto installation.

Reason – Many a times the clients perform custom changes to their themes, plugins or the WordPress core as well and if upgrades are installed automatically then the site will break as the custom changes were never compatible with the new updates pushed by WordPress.

So it is advised to keep track of latest upgrades pushed by WordPress and plan out the upgrade process in order to make sure there is no fallout during and after the upgrade process.

13. Consistently monitor your website performance

There are tons of free online tools available that can provide you a detailed report of areas where you can improve.

monitor-website-performance

Tools like GTMetrix, Pingdom, Google Page Speed Insights etc. are really reliable and it is wise to check on more than one tool at one time so you can actually see in which areas improvements are required.

14. Consider using a dedicated server

If performing all the above steps has not met your desired expectations and you are receiving a buckload of user traffic on your website, then you might want to consider purchasing a dedicated hosting for WordPress.

While Shared hosting is a cost friendly and optimal choice but be aware that it only allocates part of the server resources to you which in peak load times might give up, in turn leaving you stranded.

Whereas, a dedicated hosting (although costly) would mean that every ounce of the server resources are allocated to your website which in turn means maximum uptime.

15. Take database backups daily

Never leave anything on chance and take a bi-weekly (if not daily) backup of your WordPress database. This will ensure that you are always ready to deal with the unexpected circumstances where your WordPress database may get compromised.

MySQL allows you to export database dumps using Command line interface.

By using the following command through the command line interface can create a backup of your database and save it on your server:

$ mysqldump --opt -u [uname] -p[pass] [dbname] > [backupfile.sql]
  • [uname] Username of database
  • [pass] Password of database
  • [dbname] Name of database
  • [backupfile.sql] Filename of database backup
  • [–opt] mysqldump option

Conclusion

Gone are the days when WordPress was just considered good for blogging. You can manage as big a business as you want with just the right approach, resource management and regular maintenance.

With these capabilities provided by WordPress to its community, the responsibilities of individual website admins has increased as well and they require a well defined plan of action to maintain and keep their WordPress database up to pace with the growth of their brand.

We hope our attempt to enlighten our readers with such strategies was a successful one.
Are you aware of some strategies that we might have missed? Feel free to share your comments with us. We will be delighted to hear your thoughts.

Guest Author: Kapil Arora

Kapil Arora is a skilled web developer at The Brihaspati Infotech – Web Design and Development Company. Besides analyzing technical requirements of the clients, Kapil also writes about conversion rate optimization and other strategies for success.

Tags: , ,