Optimizing Query used for a Shortcode

1 Views

So, I have this shortcode that produces a list of all published multisite and their pages. The network in question has about 50 sites in it. The problem is that loading the page I put the shortcode on takes an extra couple of seconds to load. So performance blows. Wondering what I can do to optimize this. Thoughts? 

<?php
class Multisite_Shortcode{

    /** Multisite parameters
    * network_id'  = $wpdb->siteid,
    * site_order'  = 'blog_id ', // blog_id, site_id, domain, path, registered, last_updated, public, archived, mature, spam, deleted, lang_id
    * site_sort'   = 'ASC', // ASC or DESC
    * public'      = null,
    * archived'    = null,
    * mature'      = null,
    * spam'        = null,
    * deleted'     = null,
    * limit'       = 100,
    * offset'      = 0,

    ** pages parameters
    * depth'       = 0,
    * child_of'    = 0,
    * exclude'     = '',
    * sort_column' = 'menu_order, post_title',
    * sort_order'  = 'ASC', // ASC or DESC
    * link_before' = '',
    * link_after'  = '',
    */

    /**
    * Shortcode initialization
    * @return void
    */
    function __construct() {
        add_shortcode( 'blog_pages', array(&$this, 'shortcode_blog_pages') );
    }

    function shortcode_blog_pages( $atts ) {
        global $wpdb;
        $args = shortcode_atts( array(
            // blog parameters
            'network_id' => $wpdb->siteid,
            'site_order' => 'blog_id ', // blog_id, site_id, domain, path, registered, last_updated, public, archived, mature, spam, deleted, lang_id
            'site_sort'  => 'ASC',
            'public'     => null,
            'archived'   => null,
            'mature'     => null,
            'spam'       => null,
            'deleted'    => null,
            'limit'      => 100,
            'offset'     => 0,

            // pages parameters
            'depth'         => 0,
            'child_of'      => 0,
            'exclude'       => '',
            'sort_column'   => 'menu_order, post_title',
            'sort_order'    => 'ASC',
            'link_before'   => '',
            'link_after'    => '',
        ), $atts );

        $html = '';

        if ( wp_is_large_network() )
            return $html;

        $query = "SELECT * FROM $wpdb->blogs WHERE 1=1 ";

        if ( isset( $args['network_id'] ) && ( is_array( $args['network_id'] ) || is_numeric( $args['network_id'] ) ) ) {
            $network_ids = implode( ',', wp_parse_id_list( $args['network_id'] ) );
            $query .= "AND site_id IN ($network_ids) ";
        }

        if ( isset( $args['public'] ) )
            $query .= $wpdb->prepare( "AND public = %d ", $args['public'] );

        if ( isset( $args['archived'] ) )
            $query .= $wpdb->prepare( "AND archived = %d ", $args['archived'] );

        if ( isset( $args['mature'] ) )
            $query .= $wpdb->prepare( "AND mature = %d ", $args['mature'] );

        if ( isset( $args['spam'] ) )
            $query .= $wpdb->prepare( "AND spam = %d ", $args['spam'] );

        if ( isset( $args['deleted'] ) )
            $query .= $wpdb->prepare( "AND deleted = %d ", $args['deleted'] );

        $query .= " ORDER BY {$args['site_order']} {$args['site_sort']} ";

        if ( isset( $args['limit'] ) && $args['limit'] ) {
            if ( isset( $args['offset'] ) && $args['offset'] )
                $query .= $wpdb->prepare( "LIMIT %d , %d ", $args['offset'], $args['limit'] );
            else
                $query .= $wpdb->prepare( "LIMIT %d ", $args['limit'] );
        }

        $site_results = $wpdb->get_results( $query, ARRAY_A );

        $html = '';

        // Add other parameter to the list pages
        $args['echo'] = false;
        $args['title_li'] = '';
        $args['walker'] = '';

        foreach( $site_results as $blog ) {
            switch_to_blog( $blog['blog_id'] );
            $list_pages = @wp_list_pages( $args );
            restore_current_blog();

            $details = get_blog_details( array( 'blog_id' => $blog['blog_id'] ) );
            $title = "<a href='{$details->siteurl}'>{$details->blogname}</a>";

            $html .= "<div id='site-pages-{$details->blog_id}' class='site-pages'>
                        <h2>$title</h2>
                        <ul>$list_pages</ul>
                    </div>";
        }

        return $html;
    }
}

new Multisite_Shortcode();
?>
Post Views: 1

Related posts

2 comments

  1. I see a few things that you might try to shortcut things:

    1. Currently a Multisite network only allows one site ID, according to the Codex page for $wpdb->siteid. Also, the site ID is defined in your wp-config.php file as a constant, BLOG_ID_CURRENT_SITE. So any reference to $wpdb->siteid can be replaced by BLOG_ID_CURRENT_SITE.

    2. Look into the new-in-3.7 function wp_get_sites(). It should be able to replace your $query = "SELECT * FROM $wpdb->blogs WHERE 1=1 "; and so forth.

    3. Instead of using restore_current_blog() after every switch_to_blog(), what you can do is something like this:

      $current_blog = get_current_blog_id();
foreach( $site_results as $blog ) {
    switch_to_blog( $blog );
    .
    .
    .
} // end of your foreach()
switch_to_blog( $current_blog );

    If I’m reading your code right, you should be able to replace any $wpdb calls with those two points. See this answer (point #3) as well — “Don’t query if you got a template tag to do the job for you”.

    References

  2. You can’t optimize your brute force code. WordPress network is simply not designed with the DB structure that will make efficient queries across sites possible.

    The best solution is to have a site option in which you store all the pages on the network (pairs of blog_id and post->ID) and update it whenever a new page is created or an existing is trashed.

Comments are closed.