Hundreds of SQL queries on archive page

• Ihor Vorotnov

• September 22, 2014 at 6:36 pm

Hey everyone,

Yesterday I moved a site to new VPS server and noticed slowdowns on some pages. Did some debugging with with ‘Query Monitor’ plugin and found out that my custom post type archive page runs 667 queries, 615 of them are ACF queries. They are executed pretty fast (at least while the post_meta table has only several thousands rows) – 0,0658s, but 615 queries?! And it uses some noticeable CPU, which is not good at all. Maybe I’m doing smth wrong?

The setup – a custom post type ‘Event’ with 12 custom fields (1 repeater, 1 file, 1 regular select, 2 dates and 7 plain text inputs). The archive page has 2 custom loops to show 20 upcoming events and 10 pas events:

// 20 upcoming events sorted by their start date
$upcoming_args = array (
  'post_type' => 'event',
  'post_status' => 'publish',
  'pagination' => false,
  'posts_per_page' => '20',
  'order' => 'ASC',
  'orderby' => 'meta_value',
  'meta_key' => 'date_from', // ACf date field
  'meta_query' => array ( array (
      'key' => 'date_from', // ACf date field
      'value' => date('Ymd'),
      'compare' => '>=' ),
  ),
);
$upcoming = new WP_Query( $upcoming_args );

// 10 pas events
$past_args = array (
  'post_type' => 'event',
  'post_status' => 'publish',
  'pagination' => false,
  'posts_per_page' => '10',
  'order' => 'DESC',
  'orderby' => 'meta_value',
  'meta_key' => 'date_from', // ACF date field
  'meta_query' => array ( array (
      'key' => 'date_from', // ACF date field
      'value' => date('Ymd'),
      'compare' => '<=' ),
  ),
);
$past = new WP_Query( $past_args );

Then I run those loops with <?php while ( $upcoming->have_posts() ) : $upcoming->the_post(); ?> and <?php while ( $past->have_posts() ) : $past->the_post(); ?>. Nothing special.

The output is a table-like list of events with some details (see screenshot attached). It contains:

– date_from (ACF date field)
– date_to (ACF date field)
– title (wp post title)
– dance_type (wp custom taxonomy)
– country (ACf text)
– city (ACF text)
– organization (ACF text)

In template loop I get all those ACf fields with this code:

$fields = get_fields();
// ACF date_from, date
echo $fields['date_from']; 
// ACF date_to, date
if( $fields['date_to'] ):
  echo $fields['date_to'];
endif;
// WP title
echo the_title();
// WP taxonomy
$dances = wp_get_post_terms( $post->ID, 'dance_type' );
foreach( $dances as $dance ):
  echo $dance->name;
endforeach;
// ACF country, text
echo $fields['country'];
// ACF city, text
echo $fields['city'];
// ACF organization, text
echo $fields['organization'];

This scenario should not be such resource intensive. But it is. And I’m going to switch from a plain text fields to relational fields (country and city will be converted to custom taxonomies, organization is a custom post type), which will cause even more additional queries.

Am I doing it wrong or this amount of queries is normal? Should I query only single values with the_field() instead of getting all fields with get_fields() function? Actually, I expected this function to do the opposite – query all custom fields for a given post only once via one single query.

Or maybe the problem is not the db and 600+ queries, but the PHP? Right now it looks like the queries run pretty fast, but page generation time is about 1.3-1.6s (and 0.2-0.4s for other pages without ACf queries).

Any ideas much appreciated!

acf-archive.....jpg

• Ihor Vorotnov

• September 22, 2014 at 7:35 pm

Did some tests and found out it was get_fields(); function causing this amount of queries. This is a bit confusing behavior. Reading documentation, I expected this function to do smth like SELECT * FROM wp_postmeta WHERE post_id = $ID. One query, run only once and cache results. The post_id column is index so it would be fast even with millions of rows. Just tested it – takes 0.0005s to complete, returns ALL custom fields associated with post, each ACf field twice – with and without underscore. Instead, it looks like this function gets all keys and runs a separate query for every key to get its value.

In documentation there’s a recommendation to use this function if:

…if you have a large amount of fields which you would prefer not to code independently.

If this function produces hundreds of queries, using it with large amount of fields will be a suicide.

Am I still getting this wrong?