Fix for speeding up Forum display

  • 0 Replies
  • 1025 Views
*

Offline quimbo

  • **
  • 3
  • +0/-0
Fix for speeding up Forum display
« on: April 17, 2017, 04:55:15 pm »
We are in the process of upgrading our site from  1.38 to xbtit.  We have many forums, topics and posts and file forum_main.php was taking 26 seconds to load.  I have added a table and modified the script.  It now loads in 0.3 seconds

Create a table

Code: [Select]
CREATE TABLE `xbtit_lastpost` (
  `forumid` int(11) NOT NULL,
  `lastpost` int(11) DEFAULT NULL,
  PRIMARY KEY (`forumid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

next, add the first 2 lines to the php file and and modify the 3rd line to this:

Code: [Select]
$truncate = do_sqlquery("Truncate table {$TABLE_PREFIX}lastpost");

$load = do_sqlquery("insert into {$TABLE_PREFIX}lastpost (forumid,lastpost) (Select forumid,MAX(lastpost) From {$TABLE_PREFIX}topics Group by forumid)");

$forums_res = do_sqlquery(" SELECT f.*, t.lastpost, t.subject, t.locked, p.userid as uid, u.username, p.added as date, p.topicid,".
                          " IF(t.lastpost<=(SELECT lastpostread FROM {$TABLE_PREFIX}readposts rp WHERE rp.userid=".((int)$CURUSER["uid"]).
                          " AND rp.topicid=t.id) OR t.lastpost IS NULL,'unlocked','unlockednew') as img FROM {$TABLE_PREFIX}forums f LEFT JOIN {$TABLE_PREFIX}topics t ON f.id=t.forumid".
                          " LEFT JOIN {$TABLE_PREFIX}posts p ON t.lastpost=p.id".
                          " LEFT JOIN {$TABLE_PREFIX}users u ON p.userid=u.id".
  " left join {$TABLE_PREFIX}lastpost lp on t.lastpost = lp.lastpost and t.forumid = lp.forumid".
  " WHERE (t.lastpost IS NULL OR t.lastpost= lp.lastpost) AND".
  " f.minclassread<=".((int)$CURUSER["id_level"]).
                          " AND f.id_parent=0 ORDER BY sort,name",true);


quimbo


 


Powered by EzPortal