chrisj
Active member
I have a table called psn_user_game_trophy which associates 3 different models and includes a date. So it is a total of 4 int(11)'s. So far the average has been at 400 per user so it can easily pass 1 million with a small forum. What kind of options should I take to ensure this table does not hurt the performance of the site?
Here is an typical query for this table:
Also I noticed I had two duplicate entry errors within two hours for two different tables. Both are updated in my resource intensive cron.
Below are the two sections of the code that the error occurred in. I believe a duplicate entry shouldn't occur unless it is a mysql or xenforo error.
Here is an typical query for this table:
PHP:
return $this->_getDb()->fetchAll($this->limitQueryResults('
SELECT *
FROM psn_user_game_trophy
INNER JOIN psn_game ON psn_user_game_trophy.game_id = psn_game.game_id
INNER JOIN psn_game_trophy ON psn_user_game_trophy.game_id = psn_game_trophy.game_id AND
psn_user_game_trophy.trophy_id = psn_game_trophy.trophy_id
WHERE ' . $whereClause . '
ORDER BY trophydate desc
', $limitOptions['limit'], $limitOptions['offset']));
Also I noticed I had two duplicate entry errors within two hours for two different tables. Both are updated in my resource intensive cron.
Below are the two sections of the code that the error occurred in. I believe a duplicate entry shouldn't occur unless it is a mysql or xenforo error.
PHP:
// find if there
$existingUserGameTrophy =
$psnUserGameTrophyModel->getPsnUserGameTrophyByUserIdGameIdTrophyId($userId, $existingGame['game_id'], $userTrophy['id']);
if(!empty($existingUserGameTrophy)){
// user already had trophy, we should never have to update this
} else {
$dwPsnUserGameTrophy = XenForo_DataWriter::create('PsnLeaderboards_DataWriter_PsnUserGameTrophy');
$dwPsnUserGameTrophy->set('user_id', $userId);
$dwPsnUserGameTrophy->set('game_id', $existingGame['game_id']);
$dwPsnUserGameTrophy->set('trophy_id', $userTrophy['id']);
$dwPsnUserGameTrophy->set('trophydate', $userTrophy['trophydate']);
$dwPsnUserGameTrophy->save();
}
PHP:
$existingUserGame = $psnUserGameModel->getPsnUserGameByUserIdGameId($userId, $psnGame['game_id']);
$dwPsnUserGame = XenForo_DataWriter::create('PsnLeaderboards_DataWriter_PsnUserGame');
if (!empty($existingUserGame['user_id']) && !empty($existingUserGame['game_id'])) {
$dwPsnUserGame->setExistingData(array($existingUserGame['user_id'], $existingUserGame['game_id']));
} else {
$dwPsnUserGame->set('user_id', $userId);
$dwPsnUserGame->set('game_id', $psnGame['game_id']);
}
$dwPsnUserGame->set('user_total', $game['total']);
$dwPsnUserGame->set('user_platinum', $game['platinum']);
$dwPsnUserGame->set('user_gold', $game['gold']);
$dwPsnUserGame->set('user_silver', $game['silver']);
$dwPsnUserGame->set('user_bronze', $game['bronze']);
$dwPsnUserGame->set('lastupdated', $game['lastupdated']);
$dwPsnUserGame->save();