r4262 update: new indexes speeding up scanIPv[46]Space functions.

Tables IPv4Allocation, IPv6Allocation, IPv4VS, IPv4RS are now indexed by the ip field value

diff --git a/ChangeLog b/ChangeLog
index 8fc25fe31884ac18b6ad5f8c58009472425125ed..dbccaf61e3b6b99cc1530c9a82800866bc9a0f34 100644 (file)
--- a/ChangeLog
+++ b/ChangeLog
@@ -1,6 +1,7 @@
 0.19.2
        bugfix: port linking was broken for some users due to open PDO cursor
        bugfix: Permissions code editor was broken with Google Chrome
+       update: new DB table indexes speeding up IP tree operations
 0.19.1 2011-02-22
        update: UI: rack lists are now reduced by common tags with object on Rackspace tab (FILTER_RACKLIST_BY_TAGS config var)
        update: 802.1q: If switch has IP interface in some VLAN linked to IP subnet, this VLAN is not pruned from switch's uplink

diff --git a/wwwroot/install.php b/wwwroot/install.php
index 39c055382b7104f4f42e5278631325c35c8a0361..205c942fe2c4eb3fabdd8d0912282625e82d6ff3 100644 (file)
--- a/wwwroot/install.php
+++ b/wwwroot/install.php
@@ -473,6 +473,7 @@ CREATE TABLE `IPv4Allocation` (
   `name` char(255) NOT NULL default '',
   `type` enum('regular','shared','virtual','router') default NULL,
   PRIMARY KEY  (`object_id`,`ip`),
+  KEY `ip` (`ip`),
   CONSTRAINT `IPv4Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB;
 
@@ -524,6 +525,7 @@ CREATE TABLE `IPv4RS` (
   `rspool_id` int(10) unsigned default NULL,
   `rsconfig` text,
   PRIMARY KEY  (`id`),
+  KEY `rsip` (`rsip`),
   UNIQUE KEY `pool-endpoint` (`rspool_id`,`rsip`,`rsport`),
   CONSTRAINT `IPv4RS-FK` FOREIGN KEY (`rspool_id`) REFERENCES `IPv4RSPool` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB;
@@ -544,7 +546,8 @@ CREATE TABLE `IPv4VS` (
   `name` char(255) default NULL,
   `vsconfig` text,
   `rsconfig` text,
-  PRIMARY KEY  (`id`)
+  PRIMARY KEY  (`id`),
+  KEY `vip` (`vip`)
 ) ENGINE=InnoDB;
 
 CREATE TABLE `IPv6Address` (
@@ -560,6 +563,7 @@ CREATE TABLE `IPv6Allocation` (
   `name` char(255) NOT NULL default '',
   `type` enum('regular','shared','virtual','router') default NULL,
   PRIMARY KEY  (`object_id`,`ip`),
+  KEY `ip` (`ip`),
   CONSTRAINT `IPv6Allocation-FK-object_id` FOREIGN KEY (`object_id`) REFERENCES `RackObject` (`id`) ON DELETE CASCADE
 ) ENGINE=InnoDB;
 

diff --git a/wwwroot/upgrade.php b/wwwroot/upgrade.php
index ffd8d0bc7bb8a3751016dcbed2dc16402f8d1830..4e89480d522490a1edf27c20194a14603b3978cb 100644 (file)
--- a/wwwroot/upgrade.php
+++ b/wwwroot/upgrade.php
@@ -1000,6 +1000,14 @@ CREATE TABLE `EntityLink` (
                        }
                        $query[] = "UPDATE Config SET varvalue = '0.19.1' WHERE varname = 'DB_VERSION'";
                        break;
+               case '0.19.2':
+                       $query = array_merge ($query, reloadDictionary ($batchid));
+                       $query[] = "ALTER TABLE IPv4Allocation ADD KEY `ip` (`ip`)";
+                       $query[] = "ALTER TABLE IPv6Allocation ADD KEY `ip` (`ip`)";
+                       $query[] = "ALTER TABLE IPv4VS ADD KEY `vip` (`vip`)";
+                       $query[] = "ALTER TABLE IPv4RS ADD KEY `rsip` (`rsip`)";
+                       $query[] = "UPDATE Config SET varvalue = '0.19.1' WHERE varname = 'DB_VERSION'";
+                       break;
                default:
                        showError ("executeUpgradeBatch () failed, because batch '${batchid}' isn't defined");
                        die;