Custom Metabox data slow query on Admin init

Question

recently my database got very large either in posts and postmeta, and when accessing the WP admin it loaded on init something like 700mb of data, and it was slowing down the entire backend. I found out that the heavy data was generated from the custom metabox init.

I am using the following method to load custom Select metaboxes with data taken with WP_Query:

add_filter( 'rwmb_meta_boxes', 'register_meta_boxes' );

function register_meta_boxes( $meta_boxes ) {

 $prefix = 'prefix_';
 global $theme_prefix;
 global $meta_boxes;

 $artist_list = array();

 $my_query = new WP_Query(array(
    'post_type' => 'artist',
    'post_status'=> 'publish', 
    'posts_per_page' => -1,
    'orderby' => 'title',
    'order'   => 'ASC'
    ));
 while ($my_query->have_posts()) : $my_query->the_post(); 
 $artist_list[get_the_id()] = get_the_title();
 endwhile;  wp_reset_query();

 $album_list = array();
 $my_query = new WP_Query(array(
    'post_type' => 'album',
    'post_status'=> 'publish', 
    'posts_per_page' => -1,
    'orderby' => 'title',
    'order'   => 'ASC'
    ));
 while ($my_query->have_posts()) : $my_query->the_post(); 
 $album_list[get_the_id()] = get_the_title();
 endwhile;  wp_reset_query();

 $meta_boxes = array();

 $meta_boxes[] = array(
    'id' => 'lyric-metabox',
    'title' => __( 'Lyric Options', 'muusico' ),
    'pages' => array('lyrics' ),
    'fields' => array(                                      
        array(
            'type' => 'heading',
            'name' => __( 'Artist Name', 'muusico' ),
            'id'   => 'fake_id', // Not used but needed for plugin
        ),          
        array(
            'name'     => __( 'Artist Name', 'muusico' ),
            'id'       => $prefix."artist_name",
            'type'     => 'select_advanced',
            'options'  => $artist_list,
            'multiple'    => false,
            'placeholder' => __( 'Select Artist Name', 'muusico' ),
        ),  
        array(
            'name'     => __( 'Feat (Second) Artist Name', 'muusico' ),
            'id'       => $prefix."second_artist_name",
            'type'     => 'select_advanced',
            'options'  => $artist_list,
            'multiple'    => false,
            'placeholder' => __( 'Select Second Artist Name', 'muusico' ),
        ),  
        array(
            'name'     => __( 'Third Artist Name', 'muusico' ),
            'id'       => $prefix."third_artist_name",
            'type'     => 'select_advanced',
            'options'  => $artist_list,
            'multiple'    => false,
            'placeholder' => __( 'Select Third Artist Name', 'muusico' ),
        ),
        array(
            'type' => 'heading',
            'name' => __( 'Album Name', 'muusico' ),
            'id'   => 'fake_id', // Not used but needed for plugin
        ),          
        array(
            'name'     => __( 'Album Name', 'muusico' ),
            'id'       => $prefix."album_name",
            'type'     => 'select_advanced',
            'options'  => $album_list,
            'multiple'    => false,
            'placeholder' => __( 'Select Album Name', 'muusico' ),
        ),
        array(
            'type' => 'heading',
            'name' => __( 'Translate Option', 'muusico' ),
            'id'   => 'fake_id', // Not used but needed for plugin
        ),
        array(
            'name'      => __('Translated Lyric',"muusico"),
            'id'        => $prefix."translated",
            'desc'      => __('Enter Translated Lyric','muusico'),
            'clone'     => false,
            'type'      => 'textarea',
            'std'       => ''
        ),
        array(
            'name'      => __('Lyric Description',"muusico"),
            'id'        => "descrizione",
            'desc'      => __('Enter Lyric Description','muusico'),
            'clone'     => false,
            'type'      => 'textarea',
            'std'       => ''
        ),
        array(
            'type' => 'heading',
            'name' => __( 'Lyrics Media', 'muusico' ),
            'id'   => 'fake_id', // Not used but needed for plugin
        ),
        array(
            'name'      => __('Video Embed Code',"muusico"),
            'id'        => $prefix."embed",
            'desc'      => __('Enter Embed Code','muusico'),
            'clone'     => false,
            'type'      => 'textarea',
            'std'       => ''
        )
    )
 );
   return $meta_boxes;
}

To be clear, the slow queries are the following:

$my_query = new WP_Query(array(
    'post_type' => 'artist',
    'post_status'=> 'publish', 
    'posts_per_page' => -1,
    'orderby' => 'title',
    'order'   => 'ASC'
    ));
 while ($my_query->have_posts()) : $my_query->the_post(); 
 $artist_list[get_the_id()] = get_the_title();
 endwhile;  wp_reset_query();

 $album_list = array();
 $my_query = new WP_Query(array(
    'post_type' => 'album',
    'post_status'=> 'publish', 
    'posts_per_page' => -1,
    'orderby' => 'title',
    'order'   => 'ASC'
    ));
 while ($my_query->have_posts()) : $my_query->the_post(); 
 $album_list[get_the_id()] = get_the_title();
 endwhile;  wp_reset_query();

If I remove the two queries above, the backend loads from 14s to <1s but when in edit page the select metaboxes are empty and I can't find a way to correctly populate those select without relying on those two slow and heavy WP_Query.

How can I manage to load metabox data in a faster way? A possible solution could be to make the metabox data to init only when in edit page or add new post page, but how can I accomplish this without making it run on init so that the admin panel doesn't weigh 700mb?

Thanks

Answer 1

I managed to solve this by changing the metabox type of the 3rd party plugin with an Ajax based select search.

In the exemple below I'm not using title anymore to filter the select but instead im directly inserting the URL of the post I need (artist/album in this case).

I know this is a workaround, but it works for my case and in hope it can help someone else, theres the code:

in metabox.php

'fields' => array(                                                  
        array(
            'name'     => __( 'Artist URL', 'theme' ),
            'id'       => $prefix."artist_name",
            'type'     => 'autocomplete',
            'options'  => admin_url( 'admin-ajax.php?action=artist_list' ),
        ), 
        ...

Then in functions.php

function get_ajax_artist_list() {
  $s = $_REQUEST[ 'term' ];
  $post_name = str_replace('https://www.example.com/artist/', '', $s);    
  $post_name = substr($post_name, 0, -1);
  $args = array(
    'name'        => $post_name,
    'post_type'   => 'artist',
    'post_status' => 'publish',
    'numberposts' => 1
  );
  $my_posts = get_posts($args);
  $response = array(
    array( 'value' => $my_posts[0]->ID, 'label' => $my_posts[0]->post_title ),
  );
  echo wp_json_encode( $response );
  die;
}
add_action( 'wp_ajax_artist_list', 'get_ajax_artist_list');

Thanks to @Tom J Nowell for the support.