[Done 1.4.x & 1.3.x] Speedup your Boardindex NEW Version 2!

[jthorpe]

I'm also running 1.4.0 right now.  I don't plan on upgrading until 2.0 has been out, or has been upgraded at least once.  I've done so many hacks that it would take me months to get everything done.  Not only that, but my version of 1.4.0 is lightning fast right now and I figured the features can wait.  I really don't need them that bad.

[Spaceman-Spiff]

when a new topic is created, then moved without creating a redirection, the boardindex still shows that moved topic in the origin board

This bug was fixed in 1.12  

thanks, now using 1.13

[Compuart]

OK, speed problems again with eldaronline.com. The loading of Boardindex took 10 seconds or more. So I decided to investigate the speed of YaBB SE further. Found a few things can still be optimized, especially the part showing the most recent topic (or a list of recent topics). So I've made a lot of changes and the eldaronline.com board is now a fast board again.

To make these changes available to everyone I've added them to version 2 of the SpeedUpBoardIndex mod. This will not only speed up the boardindex, but also remove/optimize some general queries and queries in the messageindex/topicdisplay.

Because the mod now consists of 35 steps (instead of 10), I've devided it into sections and added comments to these sections. Many steps of this mod can be done independently from each other (checkout the comments to see which can and which cannot).

[Compuart]

Here's how to upgrade manually from speedupboardindex version 1.13 to 2.0:

Do all mod steps except step 1 through 10 (1 through 11 steps for versions 1.4.0 and 1.3.x).

Run a SQL query:

Code Select Expand

ALTER TABLE {$db_prefix}instant_messages ADD INDEX ('msgtime')

[firewired]

Installed 2.0 on my board with no problems during the mod process. Weirdest thing happened when I entered the site: my newsfader which used to have several announcements now just flashes "6908". The original text is intact in the admin pages though.

[newbieyabbie]

Hi.
Can you load this on a small forum (1.4.1) that is slow. 35 users, 162 topics with 424 posts.

[Compuart]

Installed 2.0 on my board with no problems during the mod process. Weirdest thing happened when I entered the site: my newsfader which used to have several announcements now just flashes "6908". The original text is intact in the admin pages though.

yep, definitely a bug... here's how to fix it:

replace (Boardindex.php):

Code Select Expand

   $newslines = explode("\n",$temp[0]);

by:

Code Select Expand

   $newslines = explode("\n",$newslines);

I will release a new version shortly which will solve this bug and also address your issue about the IM index being slow.

Can you load this on a small forum (1.4.1) that is slow. 35 users, 162 topics with 424 posts.

Sure! Though I can of course not assure it will resolve the slowdown. There might also be other factors involved.

[Compuart]

version 2.1 has been released. The above mentioned news-item bug has been fixed and a few Instant Message optimisations have been added (steps 34 through 45)

[firewired]

Thanks for the news fader fix and v2.1, but I tried to download the update and the link seems to be down. Still, this is proving to be invaluable to us -- and probably to YaBB in general. Thanks again.

[Compuart]

something went wrong with the upload. It should be online now.

Because two steps were eliminated, upgrading from 2.0 to 2.1 is a little complicated. Either uninstall 2.0 and install 2.1 or do the following:

replace (InstantMessages.php):

Code Select Expand

   $request = mysql_query("UPDATE {$db_prefix}instant_messages SET readBy='1' WHERE (ID_MEMBER_TO=$ID_MEMBER)");
   $request = mysql_query("SELECT * FROM {$db_prefix}instant_messages WHERE (ID_MEMBER_TO='$ID_MEMBER' AND (deletedBy!=1)) ORDER BY msgtime");
by:

Code Select Expand

   $request = mysql_query("UPDATE {$db_prefix}instant_messages SET readBy=1 WHERE ID_MEMBER_TO=$ID_MEMBER AND readBy='0'");
   $request = mysql_query("SELECT ID_IM, fromname, subject, body, msgtime FROM {$db_prefix}instant_messages WHERE ID_MEMBER_TO=$ID_MEMBER AND deletedBy!=1 ORDER BY msgtime");

replace (InstantMessages.php):

Code Select Expand

   $request = mysql_query("SELECT * FROM {$db_prefix}instant_messages WHERE (ID_MEMBER_FROM=$ID_MEMBER && deletedBy != 0) ORDER BY msgtime");
by:

Code Select Expand

   $request = mysql_query("
      SELECT im.toName, im.subject, im.msgtime, im.body, im.ID_IM, IFNULL(mem.ID_MEMBER, 0) AS ID_MEMBER_TO, IFNULL(mem.realName,im.toName) AS toDisplayName, IFNULL(lo.logTime, 0) AS isOnline
      FROM {$db_prefix}instant_messages AS im
         LEFT JOIN {$db_prefix}members AS mem ON (mem.ID_MEMBER=im.ID_MEMBER_TO)
         LEFT JOIN {$db_prefix}log_online AS lo ON (lo.identity=mem.memberName)
      WHERE im.ID_IM IN (" . implode(',', $ims) . ")
      ORDER BY im.msgtime");
run steps 34, 36-42, 44, 45 of the 2.1 mod

and (of course) the bugfix:

replace (Boardindex.php):

Code Select Expand

$newslines = explode("\n",$temp[0]);
by:

Code Select Expand

$newslines = explode("\n",$newslines);

[firewired]

Got it, tried it -- and so far so good.

[groundup]

how come you don't load all of the settings with this query? Or if another script uses it before this point, why not just load all of them then and not use this query? Either way, I know you have got to be saving atleast 1 query.

Code Select Expand

$usrlng_result = mysql_query('SELECT value FROM '.$db_prefix.'settings WHERE variable=\'userLanguage\''); (ok it is what i did to the query but I know compuart understands it).

[Compuart]

yep, that would indeed save one extra query . I'll release a new version with this addition

[groundup]

when cleaning the log (error & active) it would be faster to use truncate than to delete every row.

Also, you should do a check when you change something in admin.php to see if the value is null (for instance, if censored list or the reserved list is =='') than it would truncate the table instead of exploding and all that other stuff.

check mysql docs for info on truncate.

[groundup]

could we use ceiling(`dateRegistered`) to get the latest member? That might be a good idea to do when you load the boards stats in load?