Reply To: Too much SQL queries for "get_fields" function

• John Huebner

• July 14, 2020 at 5:49 am

Most of what I’m going to say will likely not help you unless you do a lot of work to make it happen.

A repeater does not store a single value. It creates an entry in the options page for the repeater + an ACF field key reference for the repeater + an option & field key reference for each sub field in each row in the repeater. So let’s say that you have a repeater with 5 sub fields and 100 rows, the total entries in the db will be 5(subfields) * 2 * 100(rows) +2(the repeater) = 5002 options table entries for the repeater. Every call by ACF to get_option() produces multiple queries. To make matters worse, when calling get_fields('options') acf will load every options value it has ever stored for every options page.

Ways to mitigate many queries for options, most of this needs to be done before a lot of data is input

1) set autoload to true for the options page

2) use a custom post ID for your options page like “my-custom-options-slug”. When you do this you will limit the queries for “get_fields()” to a limited number of db entries. The reason is that the “option_name” in the DB will be prefixed by this slug. Otherwise every “option” saved by ACF will be retrieved no matter what options page they are associated with. This is not something that can be done after data is entered because you need to enter it all again.

3) An alternate to #2 is to use a post ID value for the options page post ID setting. This causes ACF to save the values in the meta table for a post instead of in the options page making it possible to do a single query for meta values instead of a single query for each value. This has the same downside as #2 after a lot of data is entered into the options page.

4) You could likely reduce the number of queries on your existing data by not using get_fields() for options and instead use a repeater loop and get specifically what you want. This will limit the number of queries because ACF is not attempting to load every option value saved on every options page. This will help if you have many other options values other than those associated with the repeater.

5) You can also limit the number of queires by using get_field('repeater-field-name', 'options') as this will cause ACF to only get the fields associated with the repeater, again, instead of trying to get every option value ever saved.

6) A way to mitigate this issue after the fact would be to use transients https://developer.wordpress.org/apis/handbook/transients/. You can get the values and store the array in a transient so that then next time you can see if the transient has a value and skip doing the work. This way you can do one DB query to get the entire array.