1

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', 'Theme2035_register_meta_boxes' );

function Theme2035_register_meta_boxes( $meta_boxes ) {

 $prefix = 'theme2035_';
 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

5
  • This is probably because posts_per_page is set to -1, also note that wp_reset_query is meant to be used with query_posts, which you should never use, you need to use wp_reset_postdata instead – Tom J Nowell Dec 1 '20 at 9:32
  • I changed to wp_reset_postadata and it saves up 4s of loading going to a total of 10s. It is still slow tho, the -1 paramter is necessary since I need every Artist and Album on my db in order to select it from the edit/addnew page. How can it be improved? – Silvex Dec 1 '20 at 9:38
  • pagination or ajax autocompletion? It's a little concerning that such a simple change would speed things up by multiple seconds, you shouldn't have been able to see a difference in performance from that change. Needing to retrieve and list all the posts at once is your problem, it does not scale – Tom J Nowell Dec 1 '20 at 9:41
  • The theme I am using is old and not updated, too bad I got a 400k pages site relying on it and now I have to face such things smh. I tried paginating the query, but the select only loads 1st page and so 10 elements in it. How can I accomplish pagination for those two queries with a select_advanced metabox? – Silvex Dec 1 '20 at 9:46
  • 1
    hmm then pagination is unlikely to work for you. 400k pages displayed at once is going to be slow no matter what you do, if only because it means 400k HTML tags have to be sent, and the memory footprint would be huge. AJAX based controls are your only feasible option. However, I see your metabox was implemented using a 3rd party framework, you will need too ask them how to do that as a standard answer would not be useful to you, and 3rd party plugin dev support is off topic here – Tom J Nowell Dec 1 '20 at 10:38
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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