# HG changeset patch
# User rejo
# Date 1181677268 0
# Node ID 31e17a9406ebcfd6b22ecd7addcfc17b6cc618fe
# Parent  76e20b9d45e635f54df3bb65e539f82f95bcd8fb
[feladat @ 68]
Updated some of the SQL queries to gain major performance improvements
when listing all slave and master zones.

diff -r 76e20b9d45e6 -r 31e17a9406eb credits.php
--- a/credits.php	Sun Jun 10 17:06:30 2007 +0000
+++ b/credits.php	Tue Jun 12 19:41:08 2007 +0000
@@ -6,7 +6,7 @@
 
     <p><a href="https://rejo.zenger.nl/poweradmin/">This version</a> is an adaption of the original <a href="http://www.poweradmin.org">Poweradmin</a>, version 1.2.7-patched. Poweradmin was written by <a href="http://sjeemz.nl/">Sjeemz</a> and <a href="http://www.trancer.nl/">Trancer</a>. The Poweradmin code includes patches by <a href="http://mostrey.be/">Wim Mostrey</a> and Dennis Roos. <a href="https://rejo.zenger.nl/poweradmin/">This version</a> has been patched by <a href="http://rejo.zenger.nl">Rejo Zenger</a> and includes many additional features like multi-language support, an update of the database abstraction layer, support for slave zones, support for supermasters, basic support for skins and a number of bug fixes.</p>
 
-    <p>Thanks to Balazs Petrikovics for submitting bugs and patches.</p>
+    <p>Thanks to Koert Buijzer (ideas on sql performance improvements) and Balazs Petrikovics (for bugreports and patches).</p>
 
 <?
 include_once("inc/footer.inc.php");
diff -r 76e20b9d45e6 -r 31e17a9406eb inc/record.inc.php
--- a/inc/record.inc.php	Sun Jun 10 17:06:30 2007 +0000
+++ b/inc/record.inc.php	Tue Jun 12 19:41:08 2007 +0000
@@ -889,71 +889,70 @@
 	LEFT JOIN records ON records.domain_id=domains.id
 	WHERE 1 $add ";
 	if ($letterstart!=all && $letterstart!=1) {
-	   $sqlq.=" AND domains.name LIKE '".$letterstart."%' ";
+	   $sqlq.=" AND substring(domains.name,1,1) REGEXP '^".$letterstart."' ";
 	} elseif ($letterstart==1) {
-	      $sqlq.=" AND ";
-	   for ($i=0;$i<=9;$i++) {
-	      $sqlq.="domains.name LIKE '".$i."%'";
-	      if ($i!=9) $sqlq.=" OR ";
-	   }
+	   $sqlq.=" AND substring(domains.name,1,1) REGEXP '^[[:digit:]]'";
 	}
 	$sqlq.=" GROUP BY domainname, domain_id
 	ORDER BY domainname
 	LIMIT $rowstart,$rowamount";
 
 	$result = $db->query($sqlq);
-	$result2 = $db->query($sqlq);
-
-	$andnot="";
-	while($r = $result2->fetchRow()) {
-	   $andnot.=" AND domains.id!= '".$r["domain_id"]."' ";
+	$result2 = $db->query($sqlq); 
+	
+	$numrows = $result2->numRows();
+	$i=1;
+	if ($numrows > 0) {
+		$andnot=" AND NOT domains.id IN (";
+		while($r = $result2->fetchRow()) {
+			$andnot.=$r["domain_id"];
+			if ($i < $numrows) {
+				$andnot.=",";
+				$i++;
+			}
+		}
+		$andnot.=")";
+	}
+	else
+	{
+		$andnot="";
 	}
 
-if ($letterstart!=all && $letterstart!=1) {
+	if ($letterstart!=all && $letterstart!=1) {
 
-	$sqlq = "SELECT domains.id AS domain_id,
-	count(DISTINCT record_owners.record_id) AS aantal,
-	domains.name AS domainname
-	FROM domains, record_owners,records, zones
-	WHERE record_owners.user_id = '".$_SESSION["userid"]."'
-	AND (records.id = record_owners.record_id
-	AND domains.id = records.domain_id)
-	$andnot 
-	AND domains.name LIKE '".$letterstart."%' 
-	AND (zones.domain_id != records.domain_id AND zones.owner!='".$_SESSION["userid"]."')
-	GROUP BY domainname, domain_id
-	ORDER BY domainname";
+		$sqlq = "SELECT domains.id AS domain_id,
+		count(DISTINCT record_owners.record_id) AS aantal,
+		domains.name AS domainname
+		FROM domains, record_owners,records, zones
+		WHERE record_owners.user_id = '".$_SESSION["userid"]."'
+		AND (records.id = record_owners.record_id
+		AND domains.id = records.domain_id)
+		$andnot 
+		AND domains.name LIKE '".$letterstart."%' 
+		AND (zones.domain_id != records.domain_id AND zones.owner!='".$_SESSION["userid"]."')
+		GROUP BY domainname, domain_id
+		ORDER BY domainname";
 
-	$result_extra = $db->query($sqlq);
-
-} else {
+		$result_extra = $db->query($sqlq);
 
-   for ($i=0;$i<=9;$i++) {
-        $sqlq = "SELECT domains.id AS domain_id,
-        count(DISTINCT record_owners.record_id) AS aantal,
-        domains.name AS domainname
-        FROM domains, record_owners,records, zones
-        WHERE record_owners.user_id = '".$_SESSION["userid"]."'
-        AND (records.id = record_owners.record_id
-        AND domains.id = records.domain_id)
-        $andnot 
-        AND domains.name LIKE '".$i."%' 
-        AND (zones.domain_id != records.domain_id AND zones.owner!='".$_SESSION["userid"]."')
-        GROUP BY domainname, domain_id
-        ORDER BY domainname";
+	} else {
 
-        $result_extra[$i] = $db->query($sqlq);
-   }
-
-}
+		$sqlq = "SELECT domains.id AS domain_id,
+		count(DISTINCT record_owners.record_id) AS aantal,
+		domains.name AS domainname
+		FROM domains, record_owners,records, zones
+		WHERE record_owners.user_id = '".$_SESSION["userid"]."'
+		AND (records.id = record_owners.record_id
+		AND domains.id = records.domain_id)
+		$andnot 
+		AND substring(domains.name,1,1) REGEXP '^[[:digit:]]'
+		AND (zones.domain_id != records.domain_id AND zones.owner!='".$_SESSION["userid"]."')
+		GROUP BY domainname, domain_id
+		ORDER BY domainname";
 
-/*
-	if ($result->numRows() == 0)
-	{
-		// Nothing found.
-		return -1;
+		$result_extra[$i] = $db->query($sqlq);
+
 	}
-*/
 
 	while($r = $result->fetchRow())
 	{
@@ -967,42 +966,42 @@
 	}
 
 
-if ($letterstart!=all && $letterstart!=1) {
+	if ($letterstart!=all && $letterstart!=1) {
 
-	while($r = $result_extra->fetchRow())
-        {
-               $ret[$r["domainname"]] = array(
-	       "name"          =>              $r["domainname"]."*",
-               "id"            =>              $r["domain_id"],
-               "owner"         =>              $_SESSION["userid"],
-               "numrec"        =>              $r["aantal"]
-               );
-	       $_SESSION["partial_".$r["domainname"]] = 1;
-        }
+		while($r = $result_extra->fetchRow())
+		{
+		       $ret[$r["domainname"]] = array(
+		       "name"          =>              $r["domainname"]."*",
+		       "id"            =>              $r["domain_id"],
+		       "owner"         =>              $_SESSION["userid"],
+		       "numrec"        =>              $r["aantal"]
+		       );
+		       $_SESSION["partial_".$r["domainname"]] = 1;
+		}
 
-} else {
+	} else {
 
-	foreach ($result_extra as $result_e) {
-        while($r = $result_e->fetchRow())
-        {
-               $ret[$r["domainname"]] = array(
-               "name"          =>              $r["domainname"]."*",
-               "id"            =>              $r["domain_id"],
-               "owner"         =>              $_SESSION["userid"],
-               "numrec"        =>              $r["aantal"]
-               );
-	       $_SESSION["partial_".$r["domainname"]] = 1;
-        }
+		foreach ($result_extra as $result_e) {
+		while($r = $result_e->fetchRow())
+		{
+		       $ret[$r["domainname"]] = array(
+		       "name"          =>              $r["domainname"]."*",
+		       "id"            =>              $r["domain_id"],
+		       "owner"         =>              $_SESSION["userid"],
+		       "numrec"        =>              $r["aantal"]
+		       );
+		       $_SESSION["partial_".$r["domainname"]] = 1;
+		}
+		}
+
 	}
 
-}
-
-if (empty($ret)) {
-   return -1;
-} else {
-   sort($ret);
-   return $ret;
-}
+	if (empty($ret)) {
+	   return -1;
+	} else {
+	   sort($ret);
+	   return $ret;
+	}
 
 }