Wordpress - Post meta vs separate database tables

Well, if I take the hat of a WP script kiddie, my answer would be: use post_meta, always.

However, I happen to know a thing or two about databases, so my answer is: never, ever, ever, use an EAV (aka the post_meta table) to store data that you might to need to query.

On the index front, there are basically none worth using in meta tables. So, if you're storing data type XYZ and are hoping you query all posts that have XYZ with a value of 'abc', well... good luck. (See all of the users/roles/caps related tickets in the WP trac to give you an idea of how gory it can get.)

On the join front, you quickly crash into the limit at which the optimizer decides to use a generic algorithm instead of analyzing the query when there are multiple join criteria.

Thus, no, no, no, no. Don't ever, ever, ever use a meta. Unless what's you're storing is cosmetic and will never be part of a query criteria.

It breaks down to your app. If you're storing, say, the birthdate of a movie director, than big deal. Use a meta all you want. But if you're storing, say, the release date of a movie, you'd be nuts to not use a separate table (or adding columns to the posts table) and add an index to that column.

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 wp_postmeta

+

30,000 products x 5 images each x 4 image meta for each = 600,000 rows in wp_postmeta

So with merely 30,000 products you are looking at having 1,800,000 rows in wp_postmeta.

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 meta_value)

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 wp_postmeta table.

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.

It depends on what you're doing. The WP way is to use the existing tables, as they've been designed to be flexible enough, however occasionally you'll reach a new class of data that can't be placed in an existing table, e.g. if you wanted category meta data, you could choose to create a wp_termsmeta table.

However, usually you can store your data quite comfortably in the different tables that exist, and where you store your data depends on what your plugin does.

• For general plugin settings, use the get_option() API call - this will be cached also.
• For plugin settings that a particular to an individual post, then use the custom meta data per post with get_post_meta(). This is usually plenty for what you need.

Caching is implemented within WordPress to speed up your response time also.