UBB.Developers Forums UBB.threads 6 V6 Modifications Forum Design Integration Pal 4.3 - Speeding up a bit

Dave, your addon is amazing, however, i've ripped through it and have sped up a few queries. The biggest problem i've seen so far is the query that assembles the group where clause statement.

You assemble a where clause formated (B_Board = 'BoardName' OR B_Board = 'wee' OR ....)

I tested a different approach which returns

B_Board IN ('Name1', 'Name2', 'Name3')

This approach seems to strip some time from all of the queries that use it .. i'm unsure if it has to do with what i found in the mysql documentation:

http://www.mysql.com/documentation/mysql/bychapter/manual_MySQL_Optimization.html#MySQL_indexes

In reply to:

---

These WHERE clauses do NOT use indexes:

... WHERE index_part2=1 AND index_part3=2 /* index_part_1 is not used */
... WHERE index=1 OR A=10 /* Index is not used in both AND parts */
... WHERE index_part1=1 OR index_part2=10 /* No index spans all rows */

---

In any event, the speed difference is noticeable. HTH

Ok I am no pro at this so let me see if I can understand you. Are you getting rid of the (B_Board = 'BoardName' OR B_Board = 'wee' OR ....) ?

If so this query is what keeps users from seeing board information that they do not have group access to.

Could you show me exactly the reformatting you have done to specify what boards a user has access to view when retrieving information? I would really appreciate this. I would love to speed things up but I don't want to lose having information displayed according to group access. I know I'm not the only one out there with private boards.

Here's a sample of what's it's basically done . The first part is your original that pulls the read perms into group array, i just slightly modify how it grabs the board numbers and stores them as the end where clause:


$Grouparray = split("-",$user['U_Groups']);

$gsize = sizeof($Grouparray);
$g = 0;
for ($i=0; $i<=$gsize; $i++) {
if (!ereg("^[0-9]",$Grouparray[$i])) { continue; }
$g++;
if ($g > 1) {
$groupquery .= " OR ";
}
$groupquery .= "Bo_Read_Perm LIKE '%-$Grouparray[$i]-%'";
}
}

$Sql = "SELECT Bo_Keyword
FROM w3t_Boards
WHERE ($groupquery)
";
$groupwhere = "";

$Qry = mysql_unbuffered_query($Sql);

while ($Rst = mysql_fetch_row($Qry)) {
$groupwhere .= ", '$Rst[0]'";
}

$groupwhere = "B_Board IN (" . substr($groupwhere, 1, strlen($groupwhere)) . ")";

The permsisions are still set and this would be the only portion of code you'd have to change (it would blend right into your existing queries)

Hopefully that illustrates my point a little better

Thanks bunches for the code illustration

It makes sense to me now... lol I'll make the changes for the next release and thanks again.

I was looking this over and was thinking that it adds another query but then I realized that I am pulling the boards keywords out already for other queries so it is a matter of fomatting.

In the 4.3 index page you will see this...

code:

		$Grouparray = split("-",$Groups);
		$gsize = sizeof($Grouparray);
		$groupquery = "AND (";
		$g = 0;
		for ($i=0; $i<$gsize;$i++) {
			if (!ereg("^[0-9]",$Grouparray[$i])) { continue; };
			$g++;
			if ($g > 1) {
				$groupquery .= " OR ";
			}
			$groupquery .= "Bo_Read_Perm LIKE '%-$Grouparray[$i]-%'";  
		}
		$groupquery .= ")";

		$query = "
			SELECT Bo_Keyword,Bo_Title
			FROM w3t_Boards
			WHERE Bo_Keyword != ''
			$groupquery
		";
		$sth = $dbh -> do_query($query);
		$boardquery = "AND (";
		$boardquery2 = "AND (";
		$g = 0;
		while (list ($BKeyword,$BTitle) = $dbh -> fetch_array($sth)) {
			$palBkeyword[$g] = "$BKeyword";
			$palBtitle[$g] = "$BTitle";
			$g++;
			if ($g > 1) {
				$boardquery .= " OR ";
				$boardquery2 .= " OR ";
			}
			$boardquery .= "B_Board = '$BKeyword'";
			$boardquery2 .= "w3t_Posts.B_Board = '$BKeyword'";		
		}
		$dbh -> finish_sth($sth);
		$boardquery .= ")";
		$boardquery2 .= ")";

---

The above code would be changed to this?....

code:

		$Grouparray = split("-",$Groups);
		$gsize = sizeof($Grouparray);
		$groupquery = "AND (";
		$g = 0;
		for ($i=0; $i<$gsize;$i++) {
			if (!ereg("^[0-9]",$Grouparray[$i])) { continue; };
			$g++;
			if ($g > 1) {
				$groupquery .= " OR ";
			}
			$groupquery .= "Bo_Read_Perm LIKE '%-$Grouparray[$i]-%'";  
		}
		$groupquery .= ")";

		$query = "
			SELECT Bo_Keyword,Bo_Title
			FROM w3t_Boards
			WHERE Bo_Keyword != ''
			$groupquery
		";
		$sth = $dbh -> do_query($query);
		$boardquery = "B_Board IN (";
		$boardquery2 = "AND (";
		$g = 0;
		while (list ($BKeyword,$BTitle) = $dbh -> fetch_array($sth)) {
			$palBkeyword[$g] = "$BKeyword";
			$palBtitle[$g] = "$BTitle";
			$g++;
			if ($g > 1) {
				$boardquery .= ",'$BKeyword'";

			}
			else {
				$boardquery .= "'$BKeyword'";
			}
			if ($g > 1) {
				$boardquery2 .= " OR ";
			}
			$boardquery .= "B_Board = '$BKeyword'";
			$boardquery2 .= "w3t_Posts.B_Board = '$BKeyword'";		
		}
		$dbh -> finish_sth($sth);
		$boardquery .= ")";
		$boardquery2 .= ")";

---

After making the changes to the query formatting it produced many sql errors. Is this simular to what you have done as far as the above code is concerned?

Also, just wanted to note that in 5.0(coming soon, well eventualy) the $boardquery2 is no longer needed/present.

code:
 
		$Groups = $user['U_Groups']; 
		if (!$Groups) { 
			$Groups = "-4-"; 
		} 
 
		$Grouparray = split("-",$Groups); 
		$gsize = sizeof($Grouparray); 
		$groupquery = "AND ("; 
		$g = 0; 
		for ($i=0; $i<$gsize;$i++) { 
			if (!ereg("^[0-9]",$Grouparray[$i])) { continue; }; 
			$g++; 
			if ($g > 1) { 
				$groupquery .= " OR "; 
			} 
			$groupquery .= "Bo_Read_Perm LIKE '%-$Grouparray[$i]-%'"; 
		} 
		$groupquery .= ")"; 
 
		$query = " 
			SELECT Bo_Keyword,Bo_Title 
			FROM w3t_Boards 
			WHERE Bo_Keyword != '' 
			$groupquery 
		"; 
		$sth = $dbh -> do_query($query); 
		$boardquery = "AND ("; 
		$boardquery2 = "AND ("; 
		$g = 0; 
		while (list ($BKeyword,$BTitle) = $dbh -> fetch_array($sth)) { 
			$palBkeyword[$g] = "$BKeyword"; 
			$palBtitle[$g] = "$BTitle"; 
			$g++; 
			if ($g > 1) { 
				$boardquery .= " OR "; 
				$boardquery2 .= " OR "; 
			} 
			$boardquery .= "B_Board = '$BKeyword'"; 
			$boardquery2 .= "w3t_Posts.B_Board = '$BKeyword'"; 
		} 
		$dbh -> finish_sth($sth); 
		$boardquery .= ")"; 
		$boardquery2 .= ")"; 

---

would become

code:
 
		$Groups = $user['U_Groups']; 
		if (!$Groups) { 
			$Groups = "-4-"; 
		} 
 
		$Grouparray = split("-",$Groups); 
		$gsize = sizeof($Grouparray); 
		$groupquery = "AND ("; 
		$g = 0; 
		for ($i=0; $i<$gsize;$i++) { 
			if (!ereg("^[0-9]",$Grouparray[$i])) { continue; }; 
			$g++; 
			if ($g > 1) { 
				$groupquery .= " OR "; 
			} 
			$groupquery .= "Bo_Read_Perm LIKE '%-$Grouparray[$i]-%'"; 
		} 
		$groupquery .= ")"; 
 
		// ------------------------- 
		// begin modification 
 
		$query = "SELECT Bo_Keyword 
		   	  FROM w3t_Boards 
			  WHERE ($groupquery) 
 
		$sth = $dbh -> do_query($query); 
		$boardquery = ""; 
		while (list ($BKeyword) = $dbh -> fetch_array($sth)) { 
			$boardquery .= ", '$BKeyword'"; 
		} 
		$boardquery = "B_Board IN (" . substr($boardquery, 1, strlen($boardquery)) . ")"; 
		$boardquery2 = "w3t_Posts.$boardquery"; 

---

I'm still getting errors

The 5.0 index page is not completely the same as 4.3 but the code was easy to place into the page. I still get numerous sql errors. Is this ment for a particular mysql version? I'm on an older version.

I changed the 5.0 index page to the following and it wouldn't work... maybe I'm missing something... Let me know if you see what I'm doing wrong.

code:

		$boardquery = "";


		while (list ($BKeyword,$BTitle,$BWritePerm,$BReplyPerm,$BReadPerm,$BTopicon) = $dbh -> fetch_array($sth)) {

			$palBKeyword[$BKeyword] = "$BKeyword";
			$palBTitle[$BKeyword] = "$BTitle";
			$palBWritePerm[$BKeyword] = "$BWritePerm";
			$palBReplyPerm[$BKeyword] = "$BReplyPerm";
			$palBReadPerm[$BKeyword] = "$BReadPerm";
			$palBTopicon[$BKeyword] = "$BTopicon";



			$boardquery .= ", '$BKeyword'";

			
		}
		$dbh -> finish_sth($sth);


		$boardquery = "B_Board IN (" . substr($boardquery, 1, strlen($boardquery)) . ")";

---