XF 2.0 Single query vs multiple queries?

[Jaxel]

I have an entity, this is it's structure:

    public static function getStructure(Structure $structure)
    {
        $structure->table = 'ewr_torneo_matches';
        $structure->shortName = 'EWR\Torneo:Match';
        $structure->primaryKey = 'match_id';
        $structure->columns = [
            'event_id' =>                ['type' => self::UINT, 'required' => true],
            'match_id' =>                ['type' => self::UINT, 'autoIncrement' => true],
            'match_date' =>                ['type' => self::UINT, 'required' => true],
            'match_round' =>            ['type' => self::INT, 'required' => true],
            'match_scores' =>            ['type' => self::STR, 'required' => true],
            'match_p1' =>                ['type' => self::STR, 'required' => true],
            'match_p2' =>                ['type' => self::STR, 'required' => true],
            'match_winner' =>            ['type' => self::STR, 'required' => true],
            'match_media' =>            ['type' => self::STR, 'required' => false, 'default' => ''],
        ];
        $structure->getters = [
            'round' => true,
        ];
        $structure->relations = [
            'Event' => [
                'entity' => 'EWR\Torneo:Event',
                'type' => self::TO_ONE,
                'conditions' => 'event_id',
                'primary' => true,
            ],
            'PlayerOne' => [
                'entity' => 'EWR\Torneo:Result',
                'type' => self::TO_ONE,
                'conditions' => [
                    ['event_id', '=', '$event_id'],
                    ['result_extra', '=', '$match_p1']
                ],
            ],
            'PlayerTwo' => [
                'entity' => 'EWR\Torneo:Result',
                'type' => self::TO_ONE,
                'conditions' => [
                    ['event_id', '=', '$event_id'],
                    ['result_extra', '=', '$match_p2']
                ],
            ],
        ];

        return $structure;
    }

I need to get 50 entries from this entity. If I do the following finder query, it takes a good 50 seconds to process:

    $entries = $matchRepo->findMatch()
        ->with([
            'PlayerOne',
            'PlayerOne.User',
            'PlayerTwo',
            'PlayerTwo.User'
        ])
        ->where('Event.league_id', $league->league_id)
        ->limitByPage($page, $perPage)
        ->fetch();

Now 50 seconds is a LOOOONG time.

If I instead do the following finder method, it processes in 0.0013 seconds... but results in 200 extra queries on my page.

    $entries = $matchRepo->findMatch()
        ->where('Event.league_id', $league->league_id)
        ->limitByPage($page, $perPage)
        ->fetch();

Is there anything I can do to reduce the amount of queries, while maintaining speed?

 

[DragonByte Tech]

Have you looked at the debug output to see whether there's a filesort or something going on? Have you tried applying indexes to your tables on the columns that are being joined?

(PS: Still waiting for that write-up on the Infinite Scroll, giving back to other devs after all the help you've gotten here might not be the worst idea in the world )


Fillip

 

[Jaxel]

Have you looked at the debug output to see whether there's a filesort or something going on? Have you tried applying indexes to your tables on the columns that are being joined?

(PS: Still waiting for that write-up on the Infinite Scroll, giving back to other devs after all the help you've gotten here might not be the worst idea in the world )


Fillip

Adding an index to result_extra seems to have done it...

I plan on getting to that write-up for Infinite Scroll... but I'm under time constraints right now. XF2 coming out right before SC6 comes out is crunching me to get everything done before release.

 

[Jaxel]

@DragonByte Tech okay, here is a quick rundown...

This is my template: I've commented it quite a bit to explain what certain options are

    <xf:if is="property('EWRporta_infinite') AND $page == 1 AND $total > $perPage">                            <!-- admins can disable infinite loading in a style property, does not run on page 2+ -->
        <xf:set var="$infnt">1</xf:set>                                                                        <!-- variable to let other functions know infinite loading is enabled -->
        <xf:js src="8wayrun/porta/infinite.js" />                                                            <!-- js for infinite loading -->
    </xf:if>

    <xf:if is="property('EWRporta_masonry')">                                                                <!-- admins can disable masonry in a style property -->
        <xf:set var="$msnry">1</xf:set>                                                                        <!-- variable to let other functions know masonry is enabled -->
        <xf:js src="8wayrun/porta/images.js" />                                                                <!-- js for re-organizing masonry after images finish loading -->
        <xf:js src="8wayrun/porta/masonry.js" />                                                            <!-- js for masonry grid -->
    </xf:if>

    <xf:js src="8wayrun/porta/portal.js" />                                                                    <!-- js general constructor -->



    <div class="block {{ $infnt ? 'porta-infinite' : '' }} {{ $msnry ? 'porta-masonry' : '' }}"                <!-- adds css classes depending on options -->
            data-xf-init="{{ $infnt ? 'porta-infinite' : '' }} {{ $msnry ? 'porta-masonry' : '' }}"            <!-- initializes enabled functions in the general constructor -->
            data-masonry="{$msnry}"
             data-click="{{ property('EWRporta_infinite_click') }}"                                            <!-- style property to enable click to infinite load -->
             data-after="{{ property('EWRporta_infinite_after') }}"                                            <!-- style property to define how many pages before click to load starts -->
             data-history="{{ property('EWRporta_infinite_history') }}">                                        <!-- style property to disable history/url bar changes on scrolling -->
        <xf:foreach loop="$articles" value="$article"><xf:trim>
            <xf:macro name="article_block"
                arg-article="{$article}"
                arg-catlinks="{$catlinks.{$article.thread_id}}"
                arg-attachments="{$attachments}" />
        </xf:trim></xf:foreach>
    </div>

    <xf:if is="$infnt">
        <div class="block porta-article-status">                                                            <!-- dispalys infinite loader ellipse when loading, requires css -->
            <div class="porta-article-ellipse infinite-scroll-request">
                <span class="loader-ellipse-dot"></span>
                <span class="loader-ellipse-dot"></span>
                <span class="loader-ellipse-dot"></span>
                <span class="loader-ellipse-dot"></span>
            </div>
        </div>

        <div class="block porta-article-loader">
            <xf:button class="porta-article-button button--cta">{{ phrase('EWRporta_load_more...') }}</xf:button>
        </div>
    </xf:if>



    <div class="block porta-article-pager">
        <div class="block-outer block-outer--after">
            <xf:pagenav page="{$page}" perpage="{$perPage}" total="{$total}"
                link="ewr-porta" wrapperclass="block-outer-main" />
        </div>
    </div>

The xf:pagenav section is the most important part. The infinite loader fetches the URL of the next page directly from the pagenav itself. When infinite scrolling is disabled, the pager functions as normal. When enabled, it hides the pager.

You'll notice JS calls to several libraries. Infinite-Scroll, Desandro-Masonry and ImagesLoaded are all using the most recent versions of those publicly available libraries. The general constructor runs as follows:

var EWRporta = window.EWRporta || {};

!function($, window, document)
{
    // ################################## --- ###########################################

    EWRporta.Masonry = XF.Element.newHandler(
    {
        init: function()
        {
            var $grid = this.$target.masonry(
            {
                itemSelector: '.porta-article-item',
                    // each article item is wrapped in this class
            });
       
            $grid.imagesLoaded().progress( function()
            {
                $grid.masonry('layout');
                    // executes the masonry layout a second time, after all images have finished loading
            });
        },
    });

    // ################################## --- ###########################################

    EWRporta.Infinite = XF.Element.newHandler(
    {
        init: function()
        {
            $grid = this.$target;
       
            var $scroller = $grid.infiniteScroll({
                outlayer: $grid.data('masonry'),
                    // infinite scroller plays nice with masonry if its enabled
                button: '.porta-article-button',
                    // the class of the "load more" button for clicking
                append: '.porta-article-item',
                    // in the next page, it looks for items wrapped in this class and appends it into the loader
                    // you'll notice this is the same class for article items in masonry
                hideNav: '.porta-article-pager',
                    // the pager is automatically hidden when infinite loading
                path: '.porta-article-pager .pageNav-jump--next',
                    // fetches the href tag of the next button from the pagenav pager
                status: '.porta-article-status',
                    // the class of the status box for the loading ellipsis
                history: $grid.data('history') ? 'replace' : false,
                    // enables or disables the updating of the history/url bar
            });
       
            $scroller.on('last.infiniteScroll', function()
            {
                $('.porta-article-status').hide();
                $('.porta-article-loader').hide();
                    // when no more pages are available to load, remove buttons
            });
       
            if ($grid.data('click'))
            {
                // if the admin has set the option to require a click to load more pages
       
                if ($grid.data('after'))
                {
                    // if the admin has set the option to require click only after X pages
           
                    $scroller.on('load.infiniteScroll', function onPageLoad()
                    {
                        if ($scroller.data('infiniteScroll').loadCount == $grid.data('after'))
                        {
                            $('.porta-article-loader').show();
                            $scroller.infiniteScroll('option', { loadOnScroll: false });
                            $scroller.off('load.infiniteScroll', onPageLoad);
                        }
                    });
                }
                else
                {
                    $('.porta-article-loader').show();
                    $scroller.infiniteScroll('option', { loadOnScroll: false });
                }
            }
        },
    });

    // ################################## --- ###########################################

    XF.Element.register('porta-masonry', 'EWRporta.Masonry');
    XF.Element.register('porta-infinite', 'EWRporta.Infinite');
}
(window.jQuery, window, document);

Thats it. It really is very simple.

 

[DragonByte Tech]

Thanks, I'll do some experiments tomorrow


Fillip

 

[DragonByte Tech]

I was able to get it working, I made a few minor alterations:

• push instead of replace in the history property, just because I think that's how I'd prefer to browse a site with InfScroll, but I'll keep my eye on customer feedback
• block-row instead of block for the status / button elements, because otherwise there was no padding beneath the block container and the button, which looked bad on my layout

Appreciate the write-up


Fillip