Store complex fields as JSON

• domtra

• December 3, 2019 at 7:34 pm

Hi there,

we have been using ACF Pro for numerous projects. One thing we notice on larger websites is that the DB, especially postmeta, options and other meta tables get flooded with rows when you use complex ACF fields (esp. the group, repeater and flexible content fields). This is 1) because all ACF fields add at least two rows per field to the db (field key => name mapping and the value) and 2) because for complex fields ACF stores every sub field (also with two rows per sub field) in the db.

I saw @elliotcondon tweet about maybe removing the extra row for the field key => name mapping, but I do not know what the progress is.

Also I know of the additional plugin from Hookturn ACF Custom Database Tables, that lets you save ACF fields to custom database tables. However, they do not support complex field yet. I would actually really like to see this functionality natively implemented in ACF or ACF Pro. However, sometimes, something as complex as that is not needed to improve the performance of ACF, if there was a way to optimize how fields are stored in the DB.

I have written a proof of concept, how to save the complex fields in the DB as JSON strings. Because WordPress sanitizes input partially when saving it to the DB, you have to do some manual string replacements in order to make things work. An alternative would be to store this data as PHP serialized array, as ACF already does for some things. However, I think JSON should be the way to go. Especially because newer MYSQL versions support querying JSON directly, and for older versions, querying JSON is about the same as querying serialized PHP arrays.

You can find the code here: https://gist.github.com/domtra/c78c9b8ee382df9b3a917bd21afe4ca6

It will only change the DB, when you save a post, or rather a field in the DB. Then it will still call all of the filters that would be executed by the regular update function. Also retrieving a field from the DB will execute all filters when it was saved as JSON, and it will also load non-JSON fields that were saved before this patch was active. You can disable the JSON functionality by setting ACF_STORE_AS_JSON to false or removing it.

To ensure your page still works if this patch was once active, make sure to leave the code in you codebase.

The only thing that will change if you decide to save your complex fields as JSON is how you do complex meta queries for fields. This has to be adjusted to query the JSON, and if you are using a MYSQL version that supports JSON, can make use of all of these JSON query functions.

Please note: THIS CODE IS NOT PRODUCTION READY!!!

However, I would really like to get feedback from anyone who is interested and also from ACF if they could see something like that being integrated into ACF core one day.

Cheers.