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

[Compuart]

The bugfix only applies to 1.4.0 and 1.4.1.

1.3.x doesn't need a fix

[Egg]

I get this when I try to rerun SpeedUpBoardIndex.php

YaBB SE DB Editor
Error updating the DB. SQL Error: Duplicate column name 'ID_LAST_TOPIC'
Board 1 updated!
Board 2 updated!
Board 3 updated!
Board 5 updated!
Board 6 updated!
Board 7 updated!
Board 8 updated!
Board 9 updated!
Board 10 updated!
Board 11 updated!
Board 13 updated!
Board 14 updated!
Board 15 updated!
Board 16 updated!
Board 17 updated!
Board 18 updated!
Board 19 updated!
Board 20 updated!
Board 21 updated!
 
and also I cant "repair all boards"" in admin section. it times out.

any advice??

www.200mph.org.uk

it definetly speeds up the board though! thanks

Egg

[Compuart]

I get this when I try to rerun SpeedUpBoardIndex.php

YaBB SE DB Editor
Error updating the DB. SQL Error: Duplicate column name 'ID_LAST_TOPIC'
Board 1 updated!
Board 2 updated!
Board 3 updated!
Board 5 updated!
Board 6 updated!
Board 7 updated!
Board 8 updated!
Board 9 updated!
Board 10 updated!
Board 11 updated!
Board 13 updated!
Board 14 updated!
Board 15 updated!
Board 16 updated!
Board 17 updated!
Board 18 updated!
Board 19 updated!
Board 20 updated!
Board 21 updated!

You can safely ignore this error.

and also I cant "repair all boards"" in admin section. it times out.

The mod doesn't touch the repairboards-module (eventhough strictly taken it should...). The time out is probably caused by the complexity of this module. I currently don't have a fix for this, but who knows I might one day write a mod called 'speedup repairboards'

it definetly speeds up the board though! thanks

great!

[Spaceman-Spiff]

a bit off topic:
do u think SSI.php can be enhanced too by creating another table for storing the latest topics?
function recentTopics() seems to use 4 queries
data might be a bit redundant, but speed will increase a lot

[Compuart]

hmmm, doesn't seem too hard to make this into one query, even without altering the table structure. As a matter of fact...I already did that for YaBB SE 2. Not that that's much help to you But I'll release a faster SSI after I've finished some YaBB SE 2 things.

[Spaceman-Spiff]

thanks
just wondering, wont 1 complicated query could be slower than 4 simple ones?
i mean the speed isnt always determined by the number of queries, but also the complexity of the query, isnt it?

[Compuart]

just wondering, wont 1 complicated query could be slower than 4 simple ones?
i mean the speed isnt always determined by the number of queries, but also the complexity of the query, isnt it?

That's possible. But as long as queries don't have to do something stupid (like: non-ID-joining conditions, redundant data in result set, more than one non-indexed-column condition in WHERE-part, etc.), it's almost always faster.

[Bossy Boots]

I installed this on version 1.41 installed beautifully when I remembered to run the the "speedupboardindex.php" file.
AND
It WORKS TOO.

Good job thank you.

[Nodial]

I've installed it in my 1.4.1 board and it's F A S T E R  
KEEP UP THE GOOD JOB MAN  

[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

[Webby]

Ehm....Compuart ?  R U willing to try my BoardIndex.php again   ?

[Compuart]

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  

Another bug MC600 found concerns an error when deleting a message in an empty board. This can be fixed by replacing (Subs.php):

Code Select Expand

  else
      fatal_error(mysql_error());

by:

Code Select Expand

  elseif (strlen(mysql_error()))
      fatal_error(mysql_error());
   else
      $request = mysql_query("UPDATE {$db_prefix}boards SET ID_LAST_TOPIC=0 WHERE ID_BOARD=$board");

or use the new version 1.13 instead   It can be downloaded from the first message of this topic. This version will also take the 1.4.0 and 1.3.x versions out of beta-status.

[nferreri]

I'm really interested in getting this mod to work because my index is VERY slow due to an extremely high traffic forum with lotsa boards...

BoardMod is fine up until Steps 9 and 10 where it can't find the appropriate search strings.  I attempted to manually update the files but experienced a problem with the index showing all boards even if people didn't have access to them.  Also, some people reported getting misc. SQL errors so I restored the old files.

Step 9:

boardmod file is searching for this in BoardIndex:

$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");

my boardindex has closest match of this:

$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");

not quite sure why those are different.. i don't ever recall editting that query string.


step 10:
boardmod wants to find this in boardindex:

$result3 = mysql_query("SELECT m.posterName,m.ID_MEMBER,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies, mem.realName FROM {$db_prefix}topics as t LEFT JOIN {$db_prefix}messages as m ON (m.ID_MSG=t.ID_LAST_MSG) LEFT JOIN {$db_prefix}members as mem USING (ID_MEMBER) WHERE (t.ID_BOARD=$row_board[ID_BOARD]) ORDER BY m.posterTime DESC LIMIT 1");

      if (mysql_num_rows($result3) > 0) {

         list ($latestPostName,$latestPostID,$latestPostTime,$subject,$topicID,$numReplies, $latestPostRealName) = mysql_fetch_row($result3);

         if ($username!='Guest') {

            $result4 = mysql_query("SELECT logTime FROM {$db_prefix}log_boards WHERE (ID_BOARD=$row_board[ID_BOARD] && memberName='$username' && logTime >= $latestPostTime) LIMIT 1");

            $result4b = mysql_query("SELECT logTime FROM {$db_prefix}log_mark_read WHERE (ID_BOARD=$row_board[ID_BOARD] && memberName='$username' && logTime >= $latestPostTime) LIMIT 1");

            $log1 = mysql_num_rows($result4);

            $log2 = mysql_num_rows($result4b);

         }

      }

my boardindex has something almost complete different... this is the text I replaced during my testing:

$result3 = mysql_query("SELECT m.posterName,m.ID_MEMBER,m.posterTime,m.subject,t.ID_TOPIC,t.numReplies, mem.realName, lb.logTime as log1, lmr.logTime as log2 FROM {$db_prefix}messages as m, {$db_prefix}topics as t LEFT JOIN {$db_prefix}members as mem ON (mem.ID_MEMBER=m.ID_MEMBER) LEFT JOIN {$db_prefix}log_boards as lb ON ('$username'!='Guest' && lb.ID_BOARD=$row_board[ID_BOARD] && lb.memberName='$username' && lb.logTime >= m.posterTime) LEFT JOIN {$db_prefix}log_mark_read as lmr ON ('$username'!='Guest' && lmr.ID_BOARD=$row_board[ID_BOARD] && lmr.memberName='$username' && lmr.logTime >= m.posterTime) WHERE (m.ID_MSG=t.ID_LAST_MSG && t.ID_BOARD=$row_board[ID_BOARD]) ORDER BY m.posterTime DESC LIMIT 1");
         $latestPostName = $txt[470];
         $latestPostTime = $txt[470];
         $latestPostID = '-1';
         $latestModTime = $subject = $topicID = '';
         $numReplies = 0;
         if (mysql_num_rows($result3) > 0)
            list ($latestPostName,$latestPostID,$latestPostTime,$subject,$topicID,$numReplies, $latestPostRealName, $log1, $log2) = mysql_fetch_row($result3);
         $latestEditTime = $latestModTime = $latestPostTime;

as you can see, they're very different.... if anyone could offer some assistance i would greatly appreciate it.

[Compuart]

The lines of code you are describing seem to come from the boardindex of YaBB 1.4.0. The mod you're using though is the 1.4.1 version mod. Try SpeedUpBoardIndex1_4_0.mod instead of SpeedUpBoardIndex1_4_1.mod. Both are included in the zip. Everything should work fine then (I hope )

[nferreri]

omg im so stupid... i could have sworn i had version 1.4.1 but apparently i only have 1.4.0 (did version check).

the 1.4.0 version worked great... makes me a little weary im running an old version of yabb though.. i guess ill look at features and see if the .1 is worth the pain of upgrading.

thanks for help.