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

[Compuart]

This mod is targeted at large sites which suffer from a slow boardindex (the front page of the YaBB SE forum). The mod was originally designed for Eldaronline.com where boardindex was loaded more than 50% faster.

It keeps track of the most recent topic for every board and stores it in the database. This way boardindex doesn't have to determine which message was the most recent. Thus reducing the number of queries by 3 * number_of_boards for every time boardindex is requested.

Version 2: improves several queries and eleminates queries when possible.

In order to use this MOD, run the SpeedUpBoardIndex.php at the root dir of your YaBB first! Then use boardmod to modify your files and upload the modified files to the Sources-dir.

Version 0.9 beta
Version 0.91 beta
Version 0.92 beta
Version 1.0
Version 1.1 (including BETA 1.4.0)
Version 1.11 (including BETA 1.4.0 and BETA 1.3.x)
Version 1.12 (including BETA 1.4.0 and BETA 1.3.x)
Version 1.13 (includes [1.4.1], [1.4.0] and [1.3.x])

Version 2.0 Beta (includes [1.4.1], [1.4.0] and [1.3.x])
Version 2.1 Beta (includes [1.4.1], [1.4.0] and [1.3.x])
Version 2.2 (includes [1.4.1], [1.4.0] and [1.3.x])
Version 2.3 (includes [1.4.1], [1.4.0] and [1.3.x])

Complete YaBB SE 1.4.1 safe mode install with this mod implemented
No scripts are required to be executed when installing this version.

Complete YaBB SE 1.4.1 upgrade version with this mod implemented
upgrade.php contains all database modifications, including those of this mod. Use this version to upgrade from earlier versions of YaBB SE

note: this mod will be also included in version 1.5.0 by default

[iamdamnsam]

will this work with 1.31?

[Compuart]

will this work with 1.31?

I'm affraid not. You can always try (at your own risk of course ), but boardindex has had some changes since 1.3.1

[Shadowgate]

I will definitely be installing this when I get time!

[Wiziwig]

It does not seem to want to work with 1.4.0, boardindex has the line:

Code Select Expand

   $result_boards = mysql_query ("SELECT c.name as catName, c.ID_CAT, b.ID_BOARD, b.name as boardName, b.description, b.moderators, b.numPosts, b.numTopics FROM {$db_prefix}categories as c LEFT JOIN {$db_prefix}boards as b ON (b.ID_CAT=c.ID_CAT) WHERE ('$settings[7]'='Administrator' || '$settings[7]'='Global Moderator' || FIND_IN_SET('$settings[7]',c.memberGroups)!=0 || c.memberGroups='') ORDER BY c.catOrder, b.boardOrder");

Where the mod says that line should be:

Code Select Expand

   $result_boards = mysql_query ("SELECT c.name as catName, c.ID_CAT, b.ID_BOARD, b.name as boardName, b.description, b.moderators, b.numPosts, b.numTopics, c.memberGroups FROM {$db_prefix}categories as c LEFT JOIN {$db_prefix}boards as b ON (b.ID_CAT=c.ID_CAT)$condition ORDER BY c.catOrder, c.ID_CAT, b.boardOrder, b.ID_BOARD");

Changing the code in boardindex to reflect both of your search for statements solves the problem (possibly another mod change occured ?) BUT, after changing the boardindex to reflect your boardindex, the following error occurs when I applied the mod:

An Error Has Occurred!

2: Bad arguments to implode()
(/usr/local/psa/home/vhosts/the-meeting-place.com/httpdocs/yabbse/Sources/BoardIndex.php ln 217)

[Nemesis]

*cough* Posting rules

http://www.yabb.info/community/index.php?board=158;action=display;threadid=11961  

[Gobalopper]

In the .php file there is this query:

Code Select Expand

CREATE TABLE {$db_prefix}test2 (type tinytext NOT NULL, value tinytext NOT NULL) TYPE=MyISAM;
Is that still needed because I don't see it mentioned in the .mod file.

[Compuart]

It does not seem to want to work with 1.4.0

Well, the only thing I can say is...go install 1.4.1  

*cough* Posting rules

woops, forgot to IM you, right?  

[Compuart]

In the .php file there is this query:

Code Select Expand

CREATE TABLE {$db_prefix}test2 (type tinytext NOT NULL, value tinytext NOT NULL) TYPE=MyISAM;
Is that still needed because I don't see it mentioned in the .mod file.

errr, no, it was a query from a dummy file which I forgot to remove...  

Fixed it in v 0.91

[Shadowgate]

I installed this mod and I don't notice a difference in load times....

I have 40k+ posts but I guess the huge boards may benefit?

[Compuart]

I have 40k+ posts but I guess the huge boards may benefit?

At some point the amount of users times the amount of queries cannot be handled by the capacity of the SQL server. If that point is reached, users have to wait for queries to be executed. For large boards with many users at a time or without enough SQL server capacity this sometimes happens, resulting in a 30+ seconds time for boardindex to load. If your users don't experience that kind of waiting times, you're just lucky your SQL server can handle it  

(for large board, this mod couldn't hurt though...)

[Nemesis]

I installed this mod and I don't notice a difference in load times....

I have 40k+ posts but I guess the huge boards may benefit?

Do me a favor open up template.php and remove the <yabb msn> tag and seeif it loads faster. If not put it back in.  

[Wiziwig]

Welp I did. Upgraded to 1.4.1 and it mods. BUT changes to the boardindex produces doubled message topics. for instance if I have my index like this:

General topics
 topic one
 topic two
announcements
 topic one
 topic two
Personals
  topic one
  topic two
  topic three

It displays after your changes in the boardindex to this:

General topics
  topic one
  topic two
Announcements
  topic one
  topic one
  topic two
  topic two
Personals
  topic one
  topic one
  topic one
  topic two
  topic two
  topic two
  topic three
  topic three
  topic three

Get my meaning? Changing the boardindex.php back to the original code eliminates this. The one and only other mod done to this file is mouseover effects and that change is very few lines long and is only a display code change.

Code Select Expand

<search for>
         print <<<EOT
  <tr>
    <td class="windowbg" width="6%" align="center" valign="top">$new</td>
    <td class="windowbg2" align="left" width="60%">

</search for>


<replace>
         print <<<EOT
 <tr>
   <td class="windowbg" bgcolor="$color[windowbg]" width="6%" align="center" valign="top">$new</td>
   <td class="windowbg2" bgcolor="$color[windowbg2]" onmouseover="this.style.backgroundColor='$color[windowbg]'" onmouseout="this.style.backgroundColor=''" align="left" width="60%">
</replace>


<search for>
  <td class="windowbg2" valign="middle" width="22%"><font size="1">$latestPostTime<br />$txt[yse88] <a href="$scripturl?board=$row_board[ID_BOARD];action=display;threadid=$topicID;start=$startPage;boardseen=1">$subject</a><br> $txt[525] $latestPostName</font></td>
 </tr>
</search for>


<replace>
   <td class="windowbg2" bgcolor="$color[windowbg2]" onmouseover="this.style.backgroundColor='$color[windowbg]'" onmouseout="this.style.backgroundColor=''" valign="middle" width="22%"><font size="1">$latestPostTime<BR>in <a href="$scripturl?board=$row_board[ID_BOARD];action=display;threadid=$topicID;start=$startPage">$subject</a><br> $txt[525] $latestPostName</font></td>
 </tr>

</replace>


[Webby]

will this work with 1.31?

I'm affraid not. You can always try (at your own risk of course ), but boardindex has had some changes since 1.3.1

Would it be wise to try it then ?

[Compuart]

Welp I did. Upgraded to 1.4.1 and it mods. BUT changes to the boardindex produces doubled message topics.

This is very odd. I don't have an explenation for this other than that some columns in the DB structure might not be as unique as I thought.

Anywayz, since I'm not sure how this can be solved here's my best guess:

try replacing (modified boardindex.php)

Code Select Expand

      SELECT c.name AS catName,c.ID_CAT,b.ID_BOARD,b.name AS boardName,b.description,b.moderators,b.numPosts,b.numTopics,c.memberGroups,m.posterName,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies,mem.realName,IFNULL(lb.logTime,0) AS boardTime,IFNULL(lmr.logTime,0) AS markReadTime

with:

Code Select Expand

      SELECT DISTINCT c.name AS catName,c.ID_CAT,b.ID_BOARD,b.name AS boardName,b.description,b.moderators,b.numPosts,b.numTopics,c.memberGroups,m.posterName,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies,mem.realName,IFNULL(lb.logTime,0) AS boardTime,IFNULL(lmr.logTime,0) AS markReadTime
(only thing changed is the DISTINCT keyword)

I hope you can try it in order to see if this makes the mod work. Thanx