Optimizing large WP Query [closed]

Question

I have WP Query like this:

      $map_args = array(
        'post_type'       => 'spots',
        'post_status'     => 'publish',
        'posts_per_page'  => -1
      ); 
      $markers_query = new WP_Query($map_args); ?>
      <?php if ($markers_query->have_posts()) : ?>
        <?php while ($markers_query->have_posts()) : $markers_query->the_post();
          $terms = get_the_terms( $post->ID , 'spots_categories' ); 
          $map_marker = get_field( 'map_marker', $terms[0]->taxonomy . '_' . $terms[0]->term_id ); 
          $tooltip_photo = get_the_post_thumbnail($post->ID, 'thumbnail', array('class' => 'marker-pop__photo'));
          $marker_url = esc_url(get_the_permalink()); 
          if ( $spot_location = get_field( 'spot_location' ) ) : 
            $markersArray[] = array($spot_location['lat'], $spot_location['lng'], get_the_title(), 'http://localhost/icamp/app/wp-content/themes/iCamp/images/logo-black.svg', $tooltip_photo, $marker_url );         
          endif; ?>
        <?php endwhile; ?>
      <?php endif; ?>
      <?php wp_reset_query(); ?>
      <?php wp_reset_postdata(); ?>

'spots' is my custom post type, and 'spots_categories' is my custom taxonomy. I have over 10.000 CPT in spots right now, so that query is really slow. I need all my posts (can't use pagination here), because I need to list that locations on leaflet map (pins with title, thumbnail and custom marker). Is there any better and faster way to list my custom pins with location to leaflet map? That Query is really slow.

Answer 1

The first thing I would consider is saving the resulting $markersArray into a transient - I'm not sure how much data you can fit into one transient, so you might need to split the data into couple of transients. More about transients, https://developer.wordpress.org/apis/handbook/transients/

The basic idea is to run the costly query once, store the result into one value in the wp_options table, and then use that value on any subsequent requests, skipping the query alltogether.

But with transient you need to decide when and how you invalidate and rebuild them. Do you give them some lifespan (minutes, hours, days...) and let them recreate themselves whenever the lifespan expires. Or is the transient only recreated whenever you add a new post or update an existing post. Or you could conjure a scheduled action (WP Cron, replaced with system cron for accuracy) that runs the query and updates the transient at a low-traffic time of the day, which can cause delay between post publish/update and the data actually showing on the public view.

Another option could be to skip the query on the initial php load and move it to an ajax request. So you would first show an empty map, which would get populated by a looping ajax request either to admin-ajax.php or to a custom WP REST Api endpoint. For example first fetch the first 1000 posts, when they're ready the next 1000 (i.e. page 2), and so on until all of the posts / map pins have been fetched. The pin json could then be stored to the user's browser, if needed.

I'm not a database expert, but I'd imagine you could also add a custom db table to serve as a aggregate table, which would contain appropriate columns for the pins. This table would get populated / updated whenever you would add or update a post - i.e. the save_post_{$post->post_type} action, https://developer.wordpress.org/reference/hooks/save_post_post-post_type/. Then you'd query this aggregate table with $wpdb and get all of the pin data on one go.

Or instead of an aggragate table or a transient, turn the query result into json and store it into a file, which you could then include and have the map script consume.

And if not all map pins are needed at the very beginning, get only the pins for the currently visible area with an ajax request when you know the map boundaries. Then fetch more pins when the boundaries change.

Answer 2

If those get_ routines are fetching from the WP database, then that may be the problem. It is better to combine queries together in the database rather than going back and forth between db server had your 'client'.