If your plugin is going to have A LOT of data, then using the
wp_postmeta is NOT a good idea as demonstrated below:
Taking WooCommerce as an example, in a store with ~30,000 products, there will be an average of, say, ~40 post meta (attributes and everything) per product, 5 product images per product, which means there will be ~4 image meta for each image:
30,000 products x 40 meta each = 1,200,000 rows in
30,000 products x 5 images each x 4 image meta for each = 600,000 rows in
So with merely 30,000 products you are looking at having 1,800,000 rows in
If you add more properties to your products or your product images, this number will multiply.
The problem with that is twofold:
- Self joins are very expensive with MySQL
wp_postmeta table is not indexed unless you are using later mysql versions (ie no FULLTEXT index for
To give an example from an actual case:
SELECT meta_value FROM wp_postmeta WHERE meta_key LIKE '_shipping_city'
This selects shipping city from all order details comes at a whopping ~3 seconds on an entry level dedicated server even if there are 5-10 orders. This is because the query is run from among a
wp_postmeta table which has ~3 million rows in live installation.
Even the home page comes quite slow, because the theme pulls various elements from
wp_postmeta - sliders, a few review inserts, a few other meta. In general product listing is very slow, searches are similarly slow when listing products.
You cannot fix this via any normal means. You may put Elastic Search in your server and use an Elastic Search plugin in Wordpress, you may use redis/memcached, you may use a good page cache plugin, but in the end fundamental issue will remain - fetching any amount of data from a bloated
wp_postmeta table will be slow, whenever it is done. On the server where I tested the solution I implemented below, all of these were installed and configured properly and optimized, and site worked agreeably OK for non logged in users or commonly done queries since caching plugins kicked in.
But the moment a logged in user tried to do something that was not commonly done or the crons, caching plugins, or any other utility wanted to fetch actual data from the db to cache it or do anything else, things went pig slow.
So I tried something else:
I coded a small plugin to take all product meta (postmeta for post type product) to a custom table generated by code. This plugin took all meta for each post and created a table by adding each meta as columns and inserting the values into each row. I turned the EAV format into a horizontal, flat relational format. I also had the plugin to delete postmeta from all moved products from the
While I'm at it, I moved attachment postmeta and all other post type's meta to their own tables.
Then I hooked into
get_(post_type)_meta filter to override retrieval of metadata to serve them from new custom tables.
Now the same query from earlier, which took ~ 3 seconds to fetch from
wp_postmeta takes ~0.006 seconds. The site now behaves as if it was a fresh WP installation.
Naturally, doing things the Wordpress way is better. It is actually the norm.
However, it is also obvious knowledge that EAV table is very inefficient in scaling. It is infinitely flexible and lets you store any data, but the price you pay for that, is performance. Its a fundamental trade off.
In that context, its difficult to tell someone who is intending to have a heap ton of data and - god forbid - query/search on that data to use
wp_postmeta table for sure. The performance hit will be great.
Using your custom tables will allow your data to pile up and still remain fast enough.
Just like how Pippin Williams, the creator of Easy Digital Downloads plugin mentioned he would use custom tables if he was just starting coding his plugin, if you are going to create something that will be used for long time or pile up a lot of data, it's more efficient to use your custom tables if you design them well.
You must make sure that any other plugin/addon developer has means to hook into your plugin to manipulate your data before and after retrieval of the data. If you do that, then you are pretty solid.