Blog

Custom Table for WordPress Custom Fields

I just read Tom McFarlin’s thoughts on building web apps with WP and my comment started to run long, so here I am, writing a blog post.

I agree with Tom, WP can make a great foundation for an app. I chose to use WP when I started building WP App Store and it gave me a huge head start when compared to starting with an app framework. Having the dashboard UI built and ready to customize is probably the piece that saved the most time. As Tom mentions, there’s user management, a clean API, and lots more. All of which are battle-tested by thousands of developers and millions of web sites. As I went deeper into building WP App Store though, I did learn some lessons about what works and what doesn’t when using WP for an app. In this post, I’ll describe one that I’ve been grappling with lately.

As WP App Store has become more complex, so has our data and our queries. Some of our queries are now looking pretty ugly, having a lot of joins. Here’s one that gets a report of a publisher’s sales commissions by querying the order post type and its custom field data:

SELECT pm2.meta_value as product_title, COUNT(*) as unit_count, SUM( pm1.meta_value ) as commission_total
FROM $wpdb->posts
	INNER JOIN $wpdb->postmeta AS pm1 ON pm1.post_id = $wpdb->posts.ID AND pm1.meta_key = '_commission_amount'
	INNER JOIN $wpdb->postmeta AS pm2 ON pm2.post_id = $wpdb->posts.ID AND pm2.meta_key = '_product_title'
	LEFT OUTER JOIN $wpdb->postmeta AS pm3 ON pm3.post_id = $wpdb->posts.ID AND pm3.meta_key = '_is_test'
	INNER JOIN $wpdb->postmeta AS pm4 ON pm4.post_id = $wpdb->posts.ID AND pm4.meta_key = '_publisher_id'
    INNER JOIN $wpdb->postmeta AS pm5 ON pm5.post_id = $wpdb->posts.ID AND pm5.meta_key = '_product_type'
    LEFT OUTER JOIN $wpdb->postmeta AS pm6 ON pm6.post_id = $wpdb->posts.ID AND pm6.meta_key = '_is_refunded'
WHERE pm5.meta_value = '%s'
    AND pm4.meta_value = '%s'
    AND (pm3.meta_value IS NULL OR pm3.meta_value = '' OR pm3.meta_value = '0')
    AND (pm6.meta_value IS NULL OR pm6.meta_value = '' OR pm6.meta_value = '0')
    AND $wpdb->posts.post_type = 'order'
    AND ($wpdb->posts.post_status = 'publish')
GROUP BY pm2.meta_value

Why a custom query? Why not use the WP_Query object for this? I believe WP_Query is intended for querying posts, but in this case we’re generating a report, leveraging MySQL’s awesome ability to count the number of sales, add up the commissions, and return one neat row with these numbers for each product title. This is the kind of stuff MySQL is great at doing.

Unfortunately though, custom fields were not designed for this. They were originally designed to be returned from the database and displayed, not queried. Performance is terrible when queries have to join the postmeta table many times for each custom field key. Plus, as Alex King pointed out in a recent episode of Shop Talk Show we’re querying a MySQL column of type longtext which is slow. And to top it off, the meta_value column isn’t indexed.

If we look at the custom fields in the query above, you could convert some of the custom fields into taxonomies because they would be repeated across several orders, but there is some data that is unique to a single instance of a post as well. Also, if we converted all the custom fields to taxonomies, we’d end up querying for many taxonomies in this one query, which would again result in many joins. Instead, I’ve been considering creating a custom database table for each post type with columns for each of the custom fields.

So, for the data in the example above, a custom table to hold the custom field data might look like the following:

CREATE TABLE IF NOT EXISTS `wp_pm_order` (
  `order_id` bigint(20) NOT NULL,
  `publisher_id` bigint(20) NOT NULL,
  `product_title` varchar(255) NOT NULL,
  `product_type` varchar(255) NOT NULL,
  `commission_amount` float NOT NULL,
  `is_test` tinyint(4) NOT NULL,
  `is_refunded` tinyint(4) NOT NULL
  PRIMARY KEY (`order_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Then the query above would look like this:

SELECT pm.product_title, COUNT(*) as unit_count, SUM( pm.commission_amount ) as commission_total
FROM $wpdb->posts
    INNER JOIN $wpdb->pm_order AS pm ON pm.post_id = $wpdb->posts.ID
WHERE pm.product_type = '%s'
    AND pm.publisher_id = '%s'
    AND (pm.is_test IS NULL OR pm.is_test = '' OR pm.is_test = '0')
    AND (pm.is_refunded IS NULL OR pm.is_refunded = '' OR pm.is_refunded = '0')
    AND $wpdb->posts.post_type = 'order'
    AND ($wpdb->posts.post_status = 'publish')
GROUP BY pm.product_title

We’ve gone from 6 joins down to 1 and the columns we’re now querying are of types representative of the data they contain. A major improvement to the query performance, but also much easier to read and maintain in the future.

What do you think of using custom database tables to store custom field data for an app built with WP? Maybe there’s a better way?

Thanks to Tom for reviewing and providing feedback on this post before I published it.

  • I ran into the same issues with my backend for selling my plugin. I originally built it in WP and built custom tables and queried them to display reports, orders, license, etc. It works great but a bit ugly UI wise.

    I started migrating to Pippins awesome Easy Digital Download plugin because it was more polished and had some functionality I was missing. I quickly realized that trying to do complex reporting queries on Custom Post types was going to get nasty. It can be done but you have to store a lot of data in memory and then use php to further manipulate the data and WPQuery can run multiple queries to get what you need. It was easier for me to use the built mysql sum, avg, count etc on a flat table using traditional mysql relationships.

    I’m still thinking about my solution but so far these are my options. Built in in a framework such as Laravel from scratch. Use PODs for WordPress. Pods has a great UI and you can use custom tables. You also can do relationships on these table the “PODS” way. Continue to use Easy Digital Download but also store data I need to report on in a parallel flat table.

  • Yea, I’m leaning towards building a lightweight framework to manage the custom tables over something like PODs, which is pretty complex and does a lot of different things.

  • Oddly enough, this exact topic came up over lunch at the community summit. I’m rarely an advocate of creating custom tables in WordPress, but this is one of those rarities where it makes perfect sense.

    For building plugins and other distributable systems, though, I still think it’s a bad idea. But for custom WP-powered web apps? Perfect.

  • I wonder though if a plugin like Easy Digital Downloads or WooCommerce should use custom tables to improve query performance on custom field data. For example, if you used one of these plugins and added a query in your theme to query all products for a user submitted price range. That would probably be fine if you only had a few products, but wouldn’t it slow down considerably as you added products to the database?

  • Dave Jacob

    The discussion here is similar on how to design any data system. If the amount and type of data is small (small number of fields and small number of posts) then using custom fields and/or taxonomies will work even if it is unwieldly.

    However at a certain point it is much more efficient to switch to using a relational database system via custom tables in the case of wordpress.

    For me I would rather design the wordpress site to use the custom tables from the start instead of using custom fields and hoping that I will not run into speed and efficiency problems.

    Additional work at the beginning of the process is usally much better than modifying the program after you run into problems.

  • Toni

    Hi! I found this article via Google looking for a solution for my problem.Basically I would like to make WP automatically insert custom fields data in a custom table in my DB. I have been looking for a solution for this for a few days mostly on codex but can’t seem to put it all together, could you please point me to the right direction.

  • Is there any script/code availabe which will run each time during save_post or so….. and create, add/alter new columns, update data into a flat table from wp_post, wp_postmeta table automatically ? Really, when there are lots of meta data for the post, joining the same postmeta table again and again to fetch and process data sometimes causing ‘WordPress database error MySQL server has gone away for query’ issue.

  • yeahman45

    is there a plugin that enable to create a separate table just to store the custom fields?? (like cck in drupal 6)… you still create a post type with its standard fields (title, body) then a separate table to store the custom fields? as described in this article… but i want know if there’s a plugin/framework that standardize all this.

  • Oscar Blank

    You’re right about WordPress, and that’s why I think most devs have a love-hate relationship with it. Regarding your blog post, it’s nice to talk about having such a table, but where’s the code? I’m doing what you’re talking about in my own plugin under development, but looking for some answers. The plugin really needed a table, because I want to leverage MySQL to sort/compute queries. But for instance, how do I join the custom table when in the admin area and working on a post? How about for the list of posts in the admin area?

  • Alexander Trust

    All you said is right, but yet I read a lot of articles, because I’m searching for a proper solution to make use of custom tables in WordPress. But besides ideas I seldom got presented proper solutions. This article is no different, sadly. I “know” how to create tables, but how do I access/update/store the data in the normal workflow of WordPress? Sure I can make my own php pages with html forms but this way there is no real integration with WordPress whatsoever. I would appreciate if someone can maybe provide me with a link for further reading?

    Kind regards.

  • Pingback: Storing Data in WordPress Custom Fields()

  • Peter Evtushok

    Custom tables are necessary for my case.
    I have WP Mutlisite with woocommerce.
    And for one payment system transaction_id must be unique.
    So Multisite uses separated posts tables then post_id = transaction_id is not unique.
    I have to use custom table for this porpuse.
    wp_postmeta table doesn’t work because someone code may be insert key with the same value

Comments Elsewhere