Post meta vs separate database tables

5 Views

When developing plugins that requires data storage, what’s the pros and cons of using one method or another ?

The explanation given in the codex is not detailed:

Read More

Before jumping in with a whole new
table, however, consider if storing
your plugin’s data in WordPress’ Post
Meta (a.k.a. Custom Fields) would
work. Post Meta is the preferred
method; use it when
possible/practical.

Post Views: 5

Related posts

Leave a Reply

5 comments

  1. 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.

  2. 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.

  3. 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.

  4. agreed with denis 100%. But there is a way around it.

    The problem with using the post meta for values to be querried is when the values are array’s etc. Such as this:

    array(
'key1' => 'val 1',
'key2' => 'val 2'
);

    This gets stored in the db as a serialised string, which will look something like this:

    {array["key1"]...{}...}

    So when you want to query all posts with array['key2'] = 'val 2' then wp has to pull every meta entry called array, unpack it, then test it, then go to the next. This will definately bring down your server if your site is successfull and has lots of posts, pages, custom posts etc.

    The solution is project depending, and you’ll see why. If you were to store the data as a var = val then wp will be able to search without having php to unpack every single test. To do this in the scenario above you would use some namespacing and store the meta keys:

    _array_key1 = 'val 1';
_array_key2 = 'val 2';

    then wp looking for key 2 with val 2 will be able to pull it straight away. This is project depending though. My current project relies on about 20 different dataTypes to be
    store with each custom post so the above would just create a massive table to search, seeing as how we are expecting 100’s of thousands of posts. So in that scenario a custom table is the only way.

    Hope this helps someone

  5. For my FarmVille site 🙂 I did both but never finished it because I sold it:

    1. I read the farmville xml and dumped the data in a custom table
    2. In WordPress I had custom fields automatically made for every field in that table (and some extra)
    3. Now worry on what happens if a value changes in either the table or on the other side: the custom field since they need to be continuously in sync

    I did this because I wanted on the one hand have users edit the wordpress site by entering new farmville data e.g. “a cow costs 10 coins” BUT from the integration side: IF a change in the xml ment the cow now costs “20 coins” (via the front-end editing plugin) that would be given as option after it: so that either the XML OR the user was right (sort of wiki system).

    So here is an example when using both.