XF 2.1 Huge load time when grabbing 15,000+ results

Cupara

Well-known member
I'm having huge load times when grabbing data from 4 different tables. It ranges around 60-66 secs and that is way too high for anyone to run on my site. I'm not sure if it is because my host is limiting us or if my code has flaws.

Archived Tips   Bet Clever XenForo Add on.png

I managed to get the Queries down to 16 by the way.

So here goes, the page causing this issue is:
PHP:
public function actionArchived()
    {
        $options = \XF::options();
        $db = \XF::db();
        $visitor = \XF::visitor();
        $app = \XF::app();
        $tipsFinder = \XF::finder('BetClever\Tipsters:Tips');
      
        $archivePage = max(1, $this->filterPage());
        $archivePerPage = 15;

        $tipsArchived = $tipsFinder->limitByPage($archivePage, $archivePerPage)->where('status', 'NOT LIKE', '2')->with('Events', 'User', 'Sports', 'Leagues')->fetch();
        $archiveTotal = $tipsFinder->total();
        $maxPage = ceil($archiveTotal / $archivePerPage);
      
        $this->assertCanonicalUrl($this->buildLink('home/archived', '', ['page' => $archivePage]));
        $this->assertValidPage($archivePage, $archivePerPage, $archiveTotal, 'home/archived');
      
        $viewParams = [
            'tipsArchived' => $tipsArchived,
            'archiveTotal' => $archiveTotal,
            'archivePage' => $archivePage,
            'archivePerPage' => $archivePerPage,
            'maxPage' => $maxPage
        ];

        return $this->view('BetClever\Tipsters:View', 'bc_home_archived_view', $viewParams);
    }

Here is my tips entity:
PHP:
<?php
namespace BetClever\Tipsters\Entity;

use XF\Mvc\Entity\Entity;
use XF\Mvc\Entity\Structure;

class Tips extends Entity
{
    public static function getStructure(Structure $structure)
    {
        $structure->table = 'tips';
        $structure->shortName = 'BetClever\Tipsters:Tips';
        $structure->contentType = 'bc_tips';
        $structure->primaryKey = 'id';
        $structure->columns = [
            'id'                 => ['type' => self::UINT, 'maxLength' => 50, 'autoIncrement' => true],
            'sport_id'            => ['type' => self::UINT, 'maxLength' => 50],
            'league_id'            => ['type' => self::UINT, 'maxLength' => 50],
            'match_id'            => ['type' => self::UINT, 'maxLength' => 50],
            'match_date'        => ['type' => self::UINT],
            'match_time'        => ['type' => self::UINT, 'nullable' => true],
            'bet_name'            => ['type' => self::STR, 'maxLength' => 255],
            'bet_id'            => ['type' => self::UINT, 'maxLength' => 50],
            'choice_id'            => ['type' => self::UINT, 'maxLength' => 50],
            'choice_name'        => ['type' => self::STR, 'maxLength' => 255],
            'odd'                => ['type' => self::FLOAT],
            'stake'                => ['type' => self::UINT, 'maxLength' => 11],
            'description'        => ['type' => self::STR],
            'other_text'        => ['type' => self::STR, 'nullable' => true],
            'user_id'            => ['type' => self::UINT, 'maxLength' => 11],
            'created_on'        => ['type' => self::UINT],
            'status'            => ['type' => self::UINT, 'maxLength' => 11],
            'winnings'            => ['type' => self::FLOAT],
            'views'                => ['type' => self::UINT, 'maxLength' => 30],
            'result'            => ['type' => self::STR, 'maxLength' => 50, 'nullable' => true]
        ];
        $structure->behaviors = [

        ];
        $structure->getters = [

        ];
        $structure->relations = [
            'Sports' => [
                'entity' => 'BetClever\Tipsters:Sports',
                'type' => self::TO_ONE,
                'conditions' => 'sport_id',
                'primary' => true
            ],
            'Leagues' => [
                'entity' => 'BetClever\Tipsters:Leagues',
                'type' => self::TO_ONE,
                'conditions' => 'league_id',
                'primary' => true
            ],
            'Bets' => [
                'entity' => 'BetClever\Tipsters:Bets',
                'type' => self::TO_ONE,
                'conditions' => 'bet_id',
                'primary' => true
            ],
            'User' => [
                'entity' => 'XF:User',
                'type' => self::TO_ONE,
                'conditions' => 'user_id',
                'primary' => true
            ],
            'Events' => [
                'entity' => 'BetClever\Tipsters:Events',
                'type' => self::TO_ONE,
                'conditions' => 'match_id',
                'primary' => true,
                'with' => 'Teams'
            ],
            'TipReports' => [
                'entity' => 'BetClever\Tipsters:TipReports',
                'type' => self::TO_MANY,
                'conditions' => 'tip_id',
                'primary' => true
            ]
        ];
        $structure->options = [
          
        ];

        return $structure;
    }

    protected function _postDelete()
    {
        $tipsRepo = \XF::repository('BetClever\Tipsters:Tips');
        $finder = \XF::em()->find('BetClever\Tipsters:Tips', $this->id);

        if ($finder)
        {
            $finder->delete($this->id);
        }
    }

}

Any input greatly appreciated.
 
Last edited:
LIMIT with large offsets is slow, try to avoid that.
But 15K shouldn't take anywhere near 60 seconds unless you DB server is extremely underpowered or bald configured.

Did you EXPLAIN the query?
 
EXPLAIN the query? Afraid not as I don't know what you mean there.

I'm just using limitByPage, is there another method for paginating results?
 
No luck as my where statement is set already and I can't alter the tables as they are tables being used for an old system and it needs to stay that way or I'll be fixing a lot of files.
 
Show the code you used to create the table and their entity structure for BetClever\Tipsters:Tips, BetClever\Tipsters:Events, BetClever\TipstersUser, BetClever\TipstersSports and BetClever\TipstersLeagues entities. Most like you forgot to make use of the indexes.
 
Kk, I'll grab those in just a few.

@batpool52! I zipped them all up to make it easier. BetClever\Tipsters:User is in the Listener file as I'm tapping into XF:User.
 

Attachments

Last edited:
The tables already exist, I'm converting a betting system from CodeIgniter to XenForo and the owner doesn't want to have the tables rewritten. I'll export the tables though and show you the setup. I can alter them but I can't rename them.

EDIT: Had to remove something from the SQL file.
 

Attachments

Last edited:
@Mythotical there are some columns which you would most likely need to index.

No luck as my where statement is set already and I can't alter the tables as they are tables being used for an old system and it needs to stay that way or I'll be fixing a lot of files.
Go the page where you get slow response then click on value for "Time" in the footer and finally share result of

  1. In the page where you're getting slow response
  2. Click on the value of "Time" stat in the footer
  3. Find the query which starts with
    Code:
    SELECT `tips`.*,
    and share the entire block which should look something like this 203945
 
Working on it, gotten 500 internal server error so skipping the loading of the page first then clicking time, I just added ?_debug=1 to the address so give me a few.

@Kirby @batpool52! something else I forgot to mention is that when I added something to my Tips.
PHP:
'Events' => [
                'entity' => 'BetClever\Tipsters:Events',
                'type' => self::TO_ONE,
                'conditions' => 'match_id',
                'primary' => true,
                'with' => 'Teams'
            ],

I added that 'with' since there was no way to match up from tips to teams so I had to call events then teams. This all started when I added that piece.
 
Last edited:
Table tips doesn't habe a usable index for the WHERE condition on field status
Table teams doesn't habe a usable index for the LEFT JOIN on field name

Also, it seems kinda wasteful to me to have that join at all, you are always joining the exact same record(s) all the time?
I think what you really wanted to have is ON (`teams_Teams_2`.`name` = `events_Events_1`.`home_team`)

PHP:
            'Teams' => [
                'entity' => 'BetClever\Tipsters:Teams',
                'type' => self::TO_ONE,
                'conditions' => [
                    ['name', '=', '$home_team']
                ],
                'primary' => true
            ],

Or do you want both Teams?

If so, you'd need two relations:
PHP:
            'HomeTeam' => [
                'entity' => 'BetClever\Tipsters:Teams',
                'type' => self::TO_ONE,
                'conditions' => [
                    ['name', '=', '$home_team']
                ],
                'primary' => true
            ],
            'AwayTeam' => [
                'entity' => 'BetClever\Tipsters:Teams',
                'type' => self::TO_ONE,
                'conditions' => [
                    ['name', '=', '$away_team']
                ],
                'primary' => true
            ],
 
Last edited:
Top Bottom