Most hosting companies offer WordPress installation using cPanel setup. It uses MySQL database in the backend and phpMyAdmin to edit the database content. You will also have tools in cPanel to manage user privileges and backup of your database. As far as we have checked, this is the same setup with the popular hosting companies like SiteGround, Bluehost and HostGator unless you use custom dedicated option. You can notice the database size will grow faster with the content you publish. In particular, few tables in MySQL database will grow much faster compared to other tables. Post meta is one such table and in this article we will discuss how to clean up wp_postmeta table in WordPress.
What is Post Meta Data?
Before we jump into the cleanup process, it is necessary to understand about meta data. This is a kind of informational data used for different purposes. WordPress stores the post related content in two tables wp_posts and wp_postmeta. The posts table wp_posts contains all details related to post, author, dates, password, status, etc. While the meta table wp_postmeta contains ADDITIONAL informational data.
Examples of Post Meta Table
Any additional meta box you see in post editor will add data in post meta table. Below are some of the best known examples:
- SEO plugins like Yoast SEO meta boxes.
- Review plugins like WP Review.
- Commercial themes add meta boxes for layout settings and other features
- Custom fields created by plugins and themes.
Though you can disable these meta boxes in Gutenberg editor, still each meta box will create entries in the backend table.
Check Size of wp_postmeta Table
By default, MySQL tables will have the prefix of wp_. For example, the post content table will be like wp_posts and post meta will be like wp_postmeta. However, if you have changed the prefix during installation then you will see different prefix on your database.
- Login to your cPanel hosting account.
- Click on phpMyAdmin app.
- Select your database and sort the database with “Size” column.
- You can find the biggest table on the top.
As you can see in the above screenshot, the post table is 26.6MB while post meta table is 109.8MB in size. This is a clear warning symbol that something is affecting the post meta table size and you have to work on to cleanup.
Structure of wp_postmeta Table
Post meta table has four fields with the below structure. Meta ID, post ID, meta key and meta values are the four fields available in the table. You have to focus on the meta_key to find which plugin creates the entries in table.
Finding Out the Root Cause of Large Size
It is important to first understand what causes the post meta to grow larger. As explained in the above examples, there are many other reasons you can find.
- Plugins that add meta boxes in post editor like SEO, review and social plugins.
- Social plugins that adds custom preferences for each post.
- Forums plugins like bbPress and online stores plugins like WooCommerce.
- Themes that add meta boxes for layout, background and format settings.
Remember, large database size will create problems like slow website and importing in local server or during migration to another hosting company.
Problems with Plugins That Add Meta Boxes
There are many plugins create entries in the post meta table.
Review Plugins
In the above example, the major reason was WP Review plugin which adds more than 30 entries for each post id.
The bigger problem is that the plugin adds review related entries on each post regardless of whether you use review on the post. When you have 10 review posts on a blog that has 10000 posts then you have to delete the review plugin to safeguard your site.
SEO Plugins
The same problem is with Yoast and any other SEO plugins. However, it is understandable that you need to have SEO title and description for each post. Therefore, think twice before installing any plugin with meta boxes on the editor to control the settings.
Larger Store and Forum Plugins
By nature, you should not combine store or forum to your existing blog site. This will drastically pull down the speed and increase the size in different tables including wp_posts and wp_postmeta. For example, the popular WooCommerce plugin will add hundreds of thousands of entries in post meta table for billing data, country and customer details. You can always host your store in a subdomain or subdirectory on different database to prevent the impact on your main blog.
Related Posts and Social Plugins
Related posts and social plugins will add huge load in database by querying. Especially, dynamic related posts are dangerous enough to exceed the limitations of your hosting account. Try to use in-built theme’s related post or third-party services like Taboola, Outbrain or AdSense matched content ads. Though there will be some compromise in site speed, you can protect your database safely.
Commercial Themes with Many Boxes in Editor
Even the lightweight themes like GeneratePress has many meta boxes in the editor. These meta boxes will add entry to each post and increase the size of your database. Therefore, checkout and test your site at the start before you plan to build. We recommend you to keep the global settings at site level instead of customizing for each post on the editor.
Too Many Images
Other reason for growing post meta is the use of too many images. WordPress stores the attachment meta data, compression details and alt tag details in post meta table.
When you have large number of images, it will add up to the increased size of your wp_postmeta table with the alt and meta data details. In addition, if you have image compression plugins like WP Smush, it will also add entries in the table.
Custom Fields
Most of the above mentioned plugins will create custom fields to use in the backend. Wordpress stores these custom fields in post_metadata table thus increasing the size of your database. The problem here is that even you uninstall and delete the plugins, these custom fields in old posts will not be removed. You have to manually delete them from the post editor or delete on the database directly.
Backup Before Cleanup
As a best practice, always take a backup of your database before doing any irreversible actions.
- When you are in phpMyAdmin, go to “Export” tab.
- You can quickly download the database in SQL format using the “Quick – display only the minimal options”.
- However, we recommend you to select “Custom – display all possible options” and choose the compression as “gzipped” to download the database in compressed GZIP format.
Cleanup wp_postmeta Table
Now that you know what is post meta and the data stored in wp_postmeta table.
- We strongly recommend you to delete the plugins that adds entries for each post. The best option is to move the plugins to separate installation so that you can split the database to control the size. For example, you can have store or reviews in a separate installation so that WooCommerce review plugin will not stuff the entries on post meta table. The same is applicable for having a bbPress forum.
- Check whether you can change the theme to simple and clean one that does not dump data in the backend database.
Remember, in addition to database issue many themes and plugins will create thumbnails for showcasing smaller images in sidebar and footer area. It is common to have 10 thumbnails for each single image you upload. This will consume too much storage space and slow down your entire site. So, choose your theme and plugins wisely based on your need and not looking at all those features that you never use.
Deleting Unused Post Meta Entries
There are also database optimization plugins like WP Optimize and WP Rocket allows you to cleanup the database. However, none of these plugins will delete the unused entries from wp_postmeta table. Many plugins and themes will not delete the database entries even after you remove them from your site. In order to find the unused entries, you can check the meta_key value for any single posts in wp_postmeta table.
Use the below query under SQL tab to filer and find out how many entries are there for a keyword. Below query filters the entries from WP Smush plugin with a keyword %smpro%.
SELECT * FROM wp_postmeta WHERE `meta_key` LIKE '%smpro%'
You can use keywords like order for WooCommerce, review for finding reviews and bbp for bbPress entries. Use the keyword that you find a plugin uses on the table to find how many entries a plugin or theme created in post meta table.
Once you are done with finding with unused entries, it’s time to delete them. Use the below query to delete all entries that contain the keyword “review”. Replace the keyword suitable for your case to delete the entries from post meta table.
DELETE FROM `wp_postmeta` WHERE `meta_key` LIKE '%review%';Deleting Custom Fields
In order to delete custom fields, you should know the name and where used post list. The easy way is to delete them from the database using the similar code like above. If you are worrying about deleting in database, edit the posts that have custom fields and delete them manually. However, you have to delete them one by one on each post which is a time consuming task.
If you know the time of deleting a plugin or changing theme, you can look for the posts published before that time. This will help you to focus on the correct posts that contain unused custom fields.
5 Comments
Leave your reply.