From 0732d32f508045da40fe9df607b9ef20ce2d198f Mon Sep 17 00:00:00 2001
From: xaver <xaver@ispconfig3>
Date: Tue, 06 Mar 2012 10:03:23 -0500
Subject: [PATCH] join fix - disappered in commit + Switch traffic joins, because its faster - Tested in a productive system (MySQL 5.1) as query few tausend domains 1-3 sec for Webtraffic sort, faster SQL = faster and MySQL 5.5 is much faster with joins (standard is not sorted with slow traffic querys) -> slowest part is coping the tables into temp -> more tweeks on cache... -> faster by heavy use
---
install/sql/incremental/upd_0009.sql | 132 ++++++++++++++++++++++----------------------
1 files changed, 66 insertions(+), 66 deletions(-)
diff --git a/install/sql/incremental/upd_0009.sql b/install/sql/incremental/upd_0009.sql
index 9da60c1..ba20b6b 100644
--- a/install/sql/incremental/upd_0009.sql
+++ b/install/sql/incremental/upd_0009.sql
@@ -1,66 +1,66 @@
-CREATE TABLE IF NOT EXISTS `proxy_reverse` (
- `rewrite_id` int(11) NOT NULL auto_increment,
- `sys_userid` int(11) unsigned NOT NULL default '0',
- `sys_groupid` int(11) unsigned NOT NULL default '0',
- `sys_perm_user` varchar(5) default NULL,
- `sys_perm_group` varchar(5) default NULL,
- `sys_perm_other` varchar(5) default NULL,
- `server_id` int(11) unsigned NOT NULL default '0',
- `rewrite_url_src` varchar(100) NOT NULL,
- `rewrite_url_dst` varchar(100) NOT NULL,
- `active` enum('n','y') NOT NULL default 'y',
- PRIMARY KEY (`rewrite_id`)
-) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
-
-
-CREATE TABLE IF NOT EXISTS `firewall_filter` (
- `firewall_id` int(11) unsigned NOT NULL auto_increment,
- `sys_userid` int(11) unsigned NOT NULL default '0',
- `domain_id` int(11) NOT NULL,
- `sys_groupid` int(11) unsigned NOT NULL default '0',
- `sys_perm_user` varchar(5) default NULL,
- `sys_perm_group` varchar(5) default NULL,
- `sys_perm_other` varchar(5) default NULL,
- `server_id` int(11) unsigned NOT NULL default '0',
- `rule_name` varchar(100) default NULL,
- `rule_id` int(11) default 1,
- `src_ip` varchar(20) NOT NULL,
- `src_netmask` varchar(20) NOT NULL,
- `dst_ip` varchar(20) NOT NULL,
- `dst_netmask` varchar(20) NOT NULL,
- `src_from_port` varchar(10) NOT NULL,
- `src_to_port` varchar(10) NOT NULL,
- `dst_to_port` varchar(10) NOT NULL,
- `dst_from_port` varchar(10) NOT NULL,
- `protocol` varchar(10) default 'tcp',
- `inbound_policy` enum('allow','deny','reject','limit') default 'allow',
- `outbound_policy` enum('allow','deny','reject','limit') default 'allow',
- `active` enum('n','y') NOT NULL default 'y',
- `client_id` int(11) NOT NULL,
- PRIMARY KEY (`firewall_id`)
-) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
-
-CREATE TABLE IF NOT EXISTS `firewall_forward` (
- `firewall_id` int(11) unsigned NOT NULL auto_increment,
- `sys_userid` int(11) unsigned NOT NULL default '0',
- `domain_id` int(11) NOT NULL,
- `sys_groupid` int(11) unsigned NOT NULL default '0',
- `sys_perm_user` varchar(5) default NULL,
- `sys_perm_group` varchar(5) default NULL,
- `sys_perm_other` varchar(5) default NULL,
- `server_id` int(11) unsigned NOT NULL default '0',
- `application_name` varchar(100) default NULL,
- `dst_ip` varchar(20) NOT NULL,
- `src_from_port` varchar(10) NOT NULL,
- `src_to_port` varchar(10) NOT NULL,
- `dst_to_port` varchar(10) NOT NULL,
- `dst_from_port` varchar(10) NOT NULL,
- `protocol` int(3) default 0,
- `active` enum('n','y') NOT NULL default 'y',
- `client_id` int(11) NOT NULL,
- PRIMARY KEY (`firewall_id`)
-) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
-
-alter table `server` add column `proxy_server` tinyint(1) not null after `vserver_server`;
-alter table `server` add column `firewall_server` tinyint(1) not null after `proxy_server`;
-alter table `web_domain` add column `nginx_directives` mediumtext not null after `apache_directives`;
+CREATE TABLE IF NOT EXISTS `proxy_reverse` (
+ `rewrite_id` int(11) NOT NULL auto_increment,
+ `sys_userid` int(11) unsigned NOT NULL default '0',
+ `sys_groupid` int(11) unsigned NOT NULL default '0',
+ `sys_perm_user` varchar(5) default NULL,
+ `sys_perm_group` varchar(5) default NULL,
+ `sys_perm_other` varchar(5) default NULL,
+ `server_id` int(11) unsigned NOT NULL default '0',
+ `rewrite_url_src` varchar(100) NOT NULL,
+ `rewrite_url_dst` varchar(100) NOT NULL,
+ `active` enum('n','y') NOT NULL default 'y',
+ PRIMARY KEY (`rewrite_id`)
+) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
+
+
+CREATE TABLE IF NOT EXISTS `firewall_filter` (
+ `firewall_id` int(11) unsigned NOT NULL auto_increment,
+ `sys_userid` int(11) unsigned NOT NULL default '0',
+ `domain_id` int(11) NOT NULL,
+ `sys_groupid` int(11) unsigned NOT NULL default '0',
+ `sys_perm_user` varchar(5) default NULL,
+ `sys_perm_group` varchar(5) default NULL,
+ `sys_perm_other` varchar(5) default NULL,
+ `server_id` int(11) unsigned NOT NULL default '0',
+ `rule_name` varchar(100) default NULL,
+ `rule_id` int(11) default 1,
+ `src_ip` varchar(20) NOT NULL,
+ `src_netmask` varchar(20) NOT NULL,
+ `dst_ip` varchar(20) NOT NULL,
+ `dst_netmask` varchar(20) NOT NULL,
+ `src_from_port` varchar(10) NOT NULL,
+ `src_to_port` varchar(10) NOT NULL,
+ `dst_to_port` varchar(10) NOT NULL,
+ `dst_from_port` varchar(10) NOT NULL,
+ `protocol` varchar(10) default 'tcp',
+ `inbound_policy` enum('allow','deny','reject','limit') default 'allow',
+ `outbound_policy` enum('allow','deny','reject','limit') default 'allow',
+ `active` enum('n','y') NOT NULL default 'y',
+ `client_id` int(11) NOT NULL,
+ PRIMARY KEY (`firewall_id`)
+) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS `firewall_forward` (
+ `firewall_id` int(11) unsigned NOT NULL auto_increment,
+ `sys_userid` int(11) unsigned NOT NULL default '0',
+ `domain_id` int(11) NOT NULL,
+ `sys_groupid` int(11) unsigned NOT NULL default '0',
+ `sys_perm_user` varchar(5) default NULL,
+ `sys_perm_group` varchar(5) default NULL,
+ `sys_perm_other` varchar(5) default NULL,
+ `server_id` int(11) unsigned NOT NULL default '0',
+ `application_name` varchar(100) default NULL,
+ `dst_ip` varchar(20) NOT NULL,
+ `src_from_port` varchar(10) NOT NULL,
+ `src_to_port` varchar(10) NOT NULL,
+ `dst_to_port` varchar(10) NOT NULL,
+ `dst_from_port` varchar(10) NOT NULL,
+ `protocol` int(3) default 0,
+ `active` enum('n','y') NOT NULL default 'y',
+ `client_id` int(11) NOT NULL,
+ PRIMARY KEY (`firewall_id`)
+) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
+
+alter table `server` add column `proxy_server` tinyint(1) not null after `vserver_server`;
+alter table `server` add column `firewall_server` tinyint(1) not null after `proxy_server`;
+alter table `web_domain` add column `nginx_directives` mediumtext not null after `apache_directives`;
--
Gitblit v1.9.1