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

[Compuart]

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

I think it's better to just ommit the WHERE-part so that MySQL will automatically convert it to truncate if it considers that necesary. I'll add it in the next version.

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.

Sorry, I don't understand what you mean...

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?

I'm affraid that's not possible. Ceiling only converts a number into an integer but is in MySQL not a group by function .

So CEILING(5.2) results in 6

[groundup]

woops, I meant greatest (aka max)

[Gobalopper]

I read in another thread that there is a 1.4.2 coming out. Any plans to include these changes with it as they really help load times.

[Spaceman-Spiff]

i never read any threads like that
afaik the next version is YSE2
and i bet the boardindex there will kick ass

[Gobalopper]

Thanks dude

I'm working to add this cookie fix into 1.4.2 also,, among other bug fixes.

Implemented atm, we just need to do some testing of 1.4.2 before releasing

I just added a nice function to split the url into domain & directory.

ja ne!

[Compuart]

woops, I meant greatest (aka max)

ah, ok, max. Yeah, that would be faster than the current boardindex. But storing this information as redundant information is still fastest. I'll check-out the speed differences.

Implemented atm, we just need to do some testing of 1.4.2 before releasing

he must have meant 1.4.1 or something like that  

[Omar Bazavilvazo]

* Omar Bazavilvazo grins demonically

now are some talks about a possible release with security fixes and some bugfixes, but well, i'll keep u ppl informed

[groundup]

You should test the speed of ALL the queries using mysql's benchmark tool.

[Compuart]

Version 2.2 adds some new improvements (as suggested above). To upgrade either uninstall 2.1 and reinstall 2.2 or:

delete (Load.php):

Code Select Expand

   /* load the settings from the table 'settings' and put them into
      an array called modSettings */
   $modSettings = array();
   $request = mysql_query("SELECT variable,value FROM {$db_prefix}settings WHERE (variable!='agreement')");
   while ($row = mysql_fetch_row($request))
         $modSettings["$row[0]"] = $row[1];

replace (index.php):

Code Select Expand

$usrlng_result = mysql_query("SELECT value FROM {$db_prefix}settings WHERE variable='userLanguage'");
$temp = mysql_fetch_array($usrlng_result);
$chkusrlng = $temp[0];
by:

Code Select Expand

$modSettings = array();
$request = mysql_query("SELECT variable,value FROM {$db_prefix}settings WHERE (variable!='agreement')");
while ($row = mysql_fetch_array($request))
      $modSettings[$row['variable']] = $row['value'];
$chkusrlng = $modSettings['userLanguage'];
and do steps 46-48 of the mod:

Code Select Expand

<Edit file>
Sources/Admin.php
</Edit file>

<Search for>
   $request = mysql_query("DELETE FROM {$db_prefix}banned WHERE 1");
</Search for>

<Replace>
   $request = mysql_query("DELETE FROM {$db_prefix}banned");
</Replace>

<Search for>
   $request = mysql_query("DELETE FROM {$db_prefix}censor WHERE 1");
</Search for>

<Replace>
   $request = mysql_query("DELETE FROM {$db_prefix}censor");
</Replace>

<Search for>
   $request = mysql_query("DELETE FROM {$db_prefix}log_online WHERE 1");
</Search for>

<Replace>
   $request = mysql_query("DELETE FROM {$db_prefix}log_online");
</Replace>

[groundup]

not sure if you want to consider this a speedup mod anymore, it is more like the upgrade for 1.4.1

[groundup]

I haven't checked if it is needed or not but it seems like there is a lot of:

Code Select Expand

   $usrlng_result = mysql_query('SELECT value FROM '.$db_prefix.'settings WHERE variable=\'userLanguage\'');
   $temp = mysql_fetch_array($usrlng_result);
   $chkusrlng = $temp[0];
   $lngfile_result = mysql_query('SELECT lngfile FROM '.$db_prefix.'members WHERE memberName=\''.$username.'\'');
   $temp = mysql_fetch_array($lngfile_result);
   $chklngfile = $temp[0];in many different files ... index.php, loginout.php, I believe in profile.php and maybe another 1 or 2.

Since it is loaded in index.php, would you need it in all of these other files?

[groundup]

I am having trouble counting this. How many queries would it reduce with the default sql for yse? and how many with more boards, messages, etc.

[Compuart]

I am having trouble counting this. How many queries would it reduce with the default sql for yse? and how many with more boards, messages, etc.

to be precise, this is the number of queries reduced for the several pages  :

• all pages: 4
• boardindex: 3*(number of boards) + 3
• boardindex + recent-topics activated: 4
• boardindex + SP1-style statistics activated: 1
• show 10 recent topics: 28 + (number of unique message authors and topic starters)
• messageindex: 2*(number of topics shown) + (number of unique message authors and topic starters) - 1
• display: 2*(number of messages shown) - 1
• IM (in- and outbox): 2*(number of topics shown) + (number of unique message authors)

furthermore there's a lot of queries optimised

[groundup]

* groundup estimates that the default boardindex is around 14-17 now

[groundup]

I think some of these complex joins are actually hindering performance. Maybe not in speed but resources certainly. My server shows a lot more load once I did these changes.