[feladat @ 68]
Updated some of the SQL queries to gain major performance improvements
when listing all slave and master zones.
--- 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");
--- 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;
+ }
}