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.

  • 4
    \$\begingroup\$ Your title is not appropriate because it describes your concern / the kind of review that you are seeking. Your title should uniquely describe what your script does. The if before while is useless, right? \$\endgroup\$ – mickmackusa Mar 20 at 0:18

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.

  • 1
    \$\begingroup\$ Thank's for answer. I solved my problem by generate file in .json with all of my spots list by cron once a day. I think that's one of the best solution for that query. I also thought about using transient, but chose that option. The transient option seems very good as well. \$\endgroup\$ – Olsz4k Apr 26 at 15:39

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

  • \$\begingroup\$ To my understanding, primarly they're shouldn't. By default WP_Query updates WP's own (object) cache with taxonomy and meta data related to the queried posts. The get_ functions hit these caches first and then either fail (empty / null values) or makes a new db query (if key is not present in the cache) and caches its result. get_field() is actually probably from Advanced Custom Fields plugin, which is just a user-friendlier layer on top of the default WP post_meta functionality. \$\endgroup\$ – Antti Koskinen Apr 24 at 21:47

Not the answer you're looking for? Browse other questions tagged or ask your own question.