Storing ACF Field Data in Custom Database Tables

Update: Our ‘ACF Custom Database Tables‘ plugin is now available for purchase!

If you’re like me & many developers I know that work on custom WordPress themes for clients, you’ll typically create multiple custom post types for a project and use ACF to define the fields/attributes for each type.

The problem

A few months ago, I was brought onto a project for a large client, they wanted to manage thousands of Property Listings on their existing high traffic, WordPress site.

As usual, I thought I’d create a custom post type for ‘Listings’, then I’d create and assign an ACF field group to this post type. I’d include custom fields for each listings ‘Attributes’ such as Address, Suburb, Number of Bedrooms, Number of Bathrooms, Price, Status etc.

As some of you may know, with this structure, when the client adds a new ‘Listing’ it’s split across multiple tables in the WordPress database. One row is created in the wp_posts table for the ‘Listing’ and one row is created for each ‘Attribute’ in the wp_post_meta table.

I was thinking, because I’ll be using ACF there would actually double the amount of rows in the wp_post_meta table . This is because two rows are created for each attribute, one row for the value and one row to link it to the field used.

It was clear from early on that this site would also require a bespoke search solution to search thousands of property listings on a range of attributes. For example, searching for all ‘2 bedroom homes, with 2 bathrooms in postcode 3000’.

I suspected that the standard WP_Query would struggle to query the ‘Listings Attributes’ if it was stored in wp_post_ meta.  I’d have tens of thousands of rows in the post meta table & searching on this data would be very slow – so I needed to come up with a better idea!

The solution

Around the same time, I was listening to the Apply Filters podcast and they were discussing popular WordPress plugins (EDD, WooCommerce etc) moving to custom tables, as it makes a lot of sense for performance & it’ll help a site ‘scale’.

I spent some more time thinking about this project and other projects that I was working on that had some similar needs. I concluded that a custom database table for the custom post type, with columns for each ‘attribute’ might be best. As this is pretty much how I’d structure the data in a custom CMS or even a spreadsheet. I assumed that structuring the data this way would result in super fast queries and help the site scale in the future.

I didn’t want to create something completely bespoke, I wanted to maintain all the features we get out of the box with a Custom Post Type in WordPress (search, list view tables, permalinks, publish status, author, dates, archives etc).

I also wanted to continue to use Advanced Custom Fields to manage the ‘Attributes’ for my ‘Listing’ post type. ACF allows me to very quickly create and easily maintain field groups with all kinds of field types. Creating and maintaining custom fields or by hand isn’t something I enjoy.

I also thought that I better automate the creation of the database tables, the linking of the rows and the creation of the columns within the table.

I solved this with a plugin I’m calling – ACF Custom Database Tables.

How it works

• You register your Custom Post Type as you normally would and define a couple of extra arguments to ‘opt in’ to create a custom table for this post type.
• You then define all of your ‘Attributes’ in a field group and assign to the Custom Post Type as you normally would.
• When this field group is published or updated the plugin does a couple of checks. First it checks if this field group is linked to a Custom Post Type that has opted in, if it is, it checks there’s a custom table for this post type, if there isn’t it’ll create one for you. Secondly, it then loops over each of the fields and checks if a column exists for this field in the custom table, if not it creates a column for each field as needed!
• Now when you publish your first ‘post’ entry in your Custom Post Type, the ‘record’ is still saved to the wp_posts table. Then only 1 row is added to the custom table, it contains all of the attributes associated with that entry.
• When you edit the post, the values are automatically loaded from the custom table and when you save the post, the values are saved to appropriate columns in the custom table.
• The plugin can also make sure no unnecessary rows are saved in the ‘post_meta’ table for this post to keep the post meta table ‘clean’ (this can be switched off if you decide you’d like to save the data into post meta as well).

A few cool things about this plugin

• I’m hooking into the ACF update/load field functions, we’re able to still use ACF on the front end in your theme as you’d expect. Functions like the_field() and get_field() all work as expected.
• All of the custom database tables and custom columns are created automatically.
• Importing / exporting data from our custom table is now super easy (it can map really well to a CSV or even a Google Sheet).
• We can still leverage all the core WP features like taxonomies if needed as we use a standard post as a ‘record’ in the posts table.
• The plugin supports all simple fields and I have made good progress on supporting the Repeater field.
• We can query this table directly in our project with one SQL query so it’s super super fast.
• We can still leverage the WP API if we expose our custom post type and we can also still easily expose the ‘meta data’.
• We could allow this to be retro fitted to existing projects (I’m still working out the details here)
• We could bring all of this functionality across ‘pages’, ’posts’ or other existing post types you might have.

Here’s a screenshot of the custom table with a bunch of demo ‘listing’ data: 

The stats

I setup a test site with 5,000 listings (stored in post meta and in a custom table with identical demo data).  I ran a query to find listings that had at least 2 bedrooms, 2 bathrooms in postcode 3005.

I used the standard WP query with a meta query, it took 0.2523 seconds to find the 13 listings. I then used wpdb->get_results on the custom table, it took 0.0076 seconds to find the same 13 listings.

The custom table query was approximately 33.2 times faster!
(I’m happy to share the database if anyone else would like to run some other comparisons).

In regards to row counts, the custom table had 5,000 rows – as expected. The post meta table had 130,000 rows (this is not a typo). This is based on only 13 Attributes (so 26 for each listing) which is actually quite low for some of the projects I’ve work on. If I had 50 attributes associated with each of the 5,000 listings I’d end up with 1 million rows in the wp_post_meta table.

If you’re still reading

Well thanks! We’ve built a brand new plugin based on the ideas in this post called ‘ACF Custom Database Tables‘, it’s now available for purchase!