U.S. DollarEuroBritish PoundCanadian DollarsAustralian DollarsIndian RupeesChina Yuan RMBMore Info →
EasyWP: How to optimize your WordPress database
Your WordPress website's database is like the engine that powers everything behind the scenes, from storing content to handling user interactions. However, without proper maintenance, it can become cluttered and inefficient, leading to issues like slow loading times, errors, and even suspensions.
Whether you're a beginner or an experienced website owner, optimizing your database is vital for ensuring the overall health and performance of your website. In this guide, we'll explore the importance of optimizing your website's database and provide practical steps to keep it clean and running smoothly.
Databases can store large amounts of information – all the posts, pages, comments, menus, and any form of text data on your website. The more developed your website is, the larger its database. The result? A more 'cluttered' database with unnecessary items: spam comments, drafts, duplicated tables, closed or temporary files, and more. A bloated database can impact your website in multiple ways, from poor performance to website stability issues.
NOTE:The database size does not relate to the overall disk space of your EasyWP subscription. Your maximum allowed database size for one website is 3GB for EasyWP Starter and EasyWP Turbo plans, and 5GB for the EasyWP Supersonic plan.
We continually improve our database setup so you experience a faster website with enhanced security. However, it’s important that you also manage your database effectively to avoid us needing to place additional restrictions.
Occasionally, a website falls under the investigation of our Legal and Abuse team when database resources become overused. There are many reasons why the database can become non-optimized, including:
To access the database and review its tables, you can use phpMyAdmin, a tool that allows you to manage your MySQL databases.
Once you are in the database, follow these steps:
1. Select your database in the left-side panel to bring up the list of tables:
2. Select the “Size” tab to sort the tables in descending order, so you can see the largest first:
3. The total size of the database tables is calculated at the bottom right of the screen, as shown below:
NOTE: If you have many tables that appear on multiple pages (as shown above), you will need to add up the totals of each page separately. Contact Namecheap Customer Support if you’re having difficulties calculating the total size of your database.
In addition to evaluating the database table sizes, you can review useful plugins to keep your website database clean and optimized.
This guide provides database optimization tips to reduce the chances of user suspensions. However, as a hosting provider, we cannot guarantee successful results and prevent user suspensions from occurring when deemed necessary by our Legal and Abuse team.
Before proceeding with any changes, we recommend that you perform a backup of your website.
Tips on how to maintain your website
1. Keep your website components up-to-date, such as the WordPress core version, themes, and plugins.
2. Avoid using third-party caching plugins. EasyWP comes with its own built-in caching. External caching plugins may interfere with this setup, leading to various issues, including frequent database queries.
3. Review and adjust the settings of your optimization, backup, and SEO plugins as required.
4. Take care of your overall website security.
If you’re unable to resolve your database issues using the below methods, consider using web resources and forums, reaching out to a database administrator specialist, or a web expert.
Duplicate database records
WordPress plugins are a great way to extend the functionality of your website. However, they can also create multiple records in your database, causing it to grow significantly.
The free version of the WP Reset plugin is an example of this issue, which creates backups in your current database instead of using cloud storage.
Tip:If you want to use the WP Reset plugin, we recommend not storing more than 3 backup versions in your database. Alternatively, remove extra versions and enable cloud storage backup (Pro version only).
Disabling or removing the WP Reset plugin is just one step to resolving the database size issue. In addition to disabling the plugin or optimizing the settings, you should clean the database manually or use Database-management plugins.
Large database tables
Think of your WordPress website like a big library, and the most important books in that library are called 'posts.' These posts are kept neatly in a special place called the 'wp_posts table.' Now, if your website has a lot of these posts, this table can get crowded pretty fast.
Sometimes, certain tools or add-ons, like Autoptimize, can make things even busier by rearranging how certain parts of your website work. This can lead to more 'records' being stored in a different part of the library called 'wp_postmeta.'
In addition to this, there are other things that can add to the clutter in your library. For example, if you have plugins like Advanced Custom Fields or WP Review, or if your website has special themes with custom colors or forms, they can all contribute to filling up this 'wp_postmeta' area.
So, if you ever peek into the back of your WordPress website and see something like this:
8.5 gigabytes of stuff in 'wp_postmeta'
3.1 gigabytes in 'wp_posts'
11 megabytes in 'wp_options'
Don't worry, it just means your library is well-stocked with all the things that make your website unique.
If you face the above situation, we recommend performing the following steps:
1. If you’re using a plugin such as Autoptimize, Advanced Custom Fields, or WP Review, consider choosing an alternative plugin that covers your needs, without bloating your database. Alternatively, you may want to consider disabling the plugins if you’re not using them.
2. Try changing your WordPress theme.
3. Perform a manual cleanup of unused custom fields.
Additionally, we recommend viewing your posts in the WordPress Dashboard. If you notice posts that are not related to your website, refer to the Hacked website section.
Cluttered database
Within WordPress, there are certain elements that may not appear on your website but still occupy space in your database. While they may hold little relevance for you as a website administrator, they are still present and can contribute to unnecessary clutter in your database. Some of the most common culprits include:
Post revisions
Archived pages and comments
Spam comments
Unused tags
Trash items
To resolve a cluttered database, we recommend taking the following actions:
1. To remove spam comments, use plugins such as Akismet or WP Bulk Delete. For a more detailed guide, learn how to quickly batch-delete spam comments in WordPress.
2. For post revisions, you can delete these individually by editing each post, or use plugins such as WP Bulk Delete, which is great for bulk deletion. Additionally, we encourage limiting the number of maximum revisions by using plugins like WP Revisions Control or Disable Post Revision or by adding a special line to the wp-config.php file:
# if you wish to limit number of post revisions to 10 for each post define( 'WP_POST_REVISIONS', 10 );
# if you wish to disable post revisions completely define( 'WP_POST_REVISIONS', false );
3. Manually delete archived posts, unused media, tags, and post relations. This option is better suited to small-sized websites, rather than bulk operations on larger sites.
High volume of database requests
If your website starts slowing down, it may be overloaded by too many requests to its database. If this occurs, our support team will help to lower the number of requests your website is receiving by adjusting website settings.
To explore database requests on your website, you can use the Query Monitor, which enables debugging of database queries. You can find more information here.
Examples of SELECT queries
Yoast SEO, YASR, All-in-One SEO, AMP, and Contextual Related Posts are examples of plugins that can cause simultaneous database requests. However, please note this is not an exhaustive list.
Yoast SEO
Click here to expand the logs example
In this example, Yoast SEO settings are not optimized and many database queries are being generated at the same time. Take the following actions:
1. Upgrade Yoast SEO to the latest version. You can find more information here.
2. Adjust the plugin settings to reduce the number of requests.
3. Disable the plugin and look for alternatives.
YASR – Yet Another Star Rating (PRO version)
Click here to expand the logs example
Upon reviewing the queries we've received, it appears that the plugin's code may not be optimized efficiently. As a result, users may encounter performance issues or unexpected behavior. In such cases, it may be advisable for users to explore alternative plugins that fulfill similar requirements more effectively.
All-in-One SEO, AMP, Contextual Related Posts
Click here to expand the logs example
Another example of a plugin that could potentially impact database response times is the All-in-One SEO plugin. This plugin, designed to establish post relations, may heavily crawl the database, occasionally resulting in overuse and subsequent slowdowns in database response. Similar database-intensive queries can also be triggered by plugins such as AMP or Contextual Related Posts, given their comparable database interaction structures. As a solution, you can try these steps:
1. Update the plugin to the latest version.
2. Tune the plugin settings to reduce the number of requests.
3. Disable the plugin and look for alternatives.
Hacked website
If your 'wp_posts' table appears to be very large, we advise examining the list of posts on your website. If it’s similar to the screenshot below and the content of the posts does not align with your website's topic, we recommend running a malware scan and reviewing the information below on how to resolve a hacked website.
To help prevent your website from being hacked, we recommend the following:
Below is a list of database management plugins. Please read the plugin description before using them to avoid any issues or potential data loss.
Advanced Database Cleaner
Great for deleting posts and comments, Advanced Database Cleaner has plenty of features in the free version. The paid Pro version contains useful functions such as scheduled site purges and database table drops. You can find more information here.
Advanced WP Reset
A powerful plugin that allows you to reset all your previously created websites to their initial state, Advanced WP Reset allows you to delete all plugins, themes, posts, and pages, as well as clear the 'uploads' folder of the website. With Advanced WP Reset, it's crucial to have your backup in place and use the plugin with caution.
Database Cleaner
A great, user-friendly WordPress plugin that supports databases of all sizes. The free version of Database Cleaner offers both simple and expert modes. Remember to pay attention to performance-related settings in the cleaning setup. There, you can adjust the size of deletion batches to optimize CPU and memory usage during website cleanup. Additionally, in the Pro version, you'll find some minor enhancements along with the option for automatic cleaning. You can find more information here.
Optimize Database after Deleting Revisions
Offering a straightforward, one-click solution for removing all types of clutter from your website, Optimize Database after Deleting Revisions is rich in features. For example, you can analyze your database's current state before executing any actions on it. Feel free to check more information here.
WP Sweep
If you need to partially clear up website clutter or, if required, delete all tables in the database, WP Sweep might be the right choice for you. The powerful tool provides options for deleting various elements such as posts, comments, users, options, and database tables. Additionally, users have the flexibility to delete each element individually or opt for a comprehensive cleanup with just one click.
Database-monitoring plugins
Query Monitor
As a popular plugin for tracking website components that generate specific queries, Query Monitor gives you valuable stats such as identifying slow queries, average query execution time, and a list of concurrent queries. This handy plugin is recommended for customers grappling with a high volume of SQL queries from their website and unsure where to begin troubleshooting. Discover how to use Query Monitor or check out the WPBeginner guide on how to add a WordPress Query Monitor on your site.
Debug Bar
Debug Bar is another highly useful debugging plugin for WordPress. While it may not be as feature-rich as Query Monitor, it provides basic information about database queries, hooks, and PHP errors. It's lightweight and simple to use.
Keep your WordPress site database in top shape
Optimizing your WordPress website's database is crucial for maintaining its overall health, performance, and user engagement. By regularly monitoring and cleaning up your database, you can prevent issues such as slow loading times, errors, and potential visitor loss.
Implementing the strategies outlined in this guide, such as updating your website components, adjusting plugin settings, and utilizing database management and monitoring plugins, can help you keep your database in top shape.
Remember, a well-maintained database is the foundation of a successful WordPress website. By taking proactive steps to optimize your database and address any issues that arise, you can ensure that your website continues to provide an exceptional user experience and remains a valuable asset to your online presence. With the right tools and a commitment to regular maintenance, you can keep your WordPress website running smoothly and efficiently for years to come.
