Support

Account

Home Forums Add-ons Repeater Field Too much SQL queries for "get_fields" function

Helping

Too much SQL queries for "get_fields" function

    • gilles

    • July 14, 2020 at 1:32 am

    Hi.

    I have an option page with a repeater inside. There are many entries in this repeater.

    When I do
    $my_options = get_fields(‘options’);
    this generate 4000 SQL queries.

    This slows the website.

    I am very surprised of this huge number of SQL queries because that all the values from the repeater were saved in the same field data field.

    Is there a way to make my site more quick and to low the amount of SQL queries ?

    Thx
    Gilles

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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.

We use cookies to offer you a better browsing experience, analyze site traffic and personalize content. Read about how we use cookies and how you can control them in our Cookie Policy. If you continue to use this site, you consent to our use of cookies.