You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@guacamole.apache.org by vn...@apache.org on 2018/01/05 15:42:12 UTC
[12/17] guacamole-client git commit: GUACAMOLE-394: Cover both main
object ID and "start_date" columns with an index for sake of correlated
MAX(start_date) history queries.
GUACAMOLE-394: Cover both main object ID and "start_date" columns with an index for sake of correlated MAX(start_date) history queries.
Project: http://git-wip-us.apache.org/repos/asf/guacamole-client/repo
Commit: http://git-wip-us.apache.org/repos/asf/guacamole-client/commit/dbd5b982
Tree: http://git-wip-us.apache.org/repos/asf/guacamole-client/tree/dbd5b982
Diff: http://git-wip-us.apache.org/repos/asf/guacamole-client/diff/dbd5b982
Branch: refs/heads/master
Commit: dbd5b982508ce098f3d88f708402820dac3b6188
Parents: 5805471
Author: Michael Jumper <mj...@apache.org>
Authored: Mon Dec 11 23:11:57 2017 -0800
Committer: Michael Jumper <mj...@apache.org>
Committed: Mon Dec 11 23:51:57 2017 -0800
----------------------------------------------------------------------
.../schema/001-create-schema.sql | 2 ++
.../schema/upgrade/upgrade-pre-0.9.14.sql | 7 +++++++
.../schema/001-create-schema.sql | 6 ++++++
.../schema/upgrade/upgrade-pre-0.9.14.sql | 10 ++++++++++
.../schema/001-create-schema.sql | 6 ++++++
5 files changed, 31 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/dbd5b982/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql
index d65bd33..f26d2cc 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/001-create-schema.sql
@@ -336,6 +336,7 @@ CREATE TABLE `guacamole_connection_history` (
KEY `sharing_profile_id` (`sharing_profile_id`),
KEY `start_date` (`start_date`),
KEY `end_date` (`end_date`),
+ KEY `connection_start_date` (`connection_id`, `start_date`),
CONSTRAINT `guacamole_connection_history_ibfk_1`
FOREIGN KEY (`user_id`)
@@ -368,6 +369,7 @@ CREATE TABLE guacamole_user_history (
KEY `user_id` (`user_id`),
KEY `start_date` (`start_date`),
KEY `end_date` (`end_date`),
+ KEY `user_start_date` (`user_id`, `start_date`),
CONSTRAINT guacamole_user_history_ibfk_1
FOREIGN KEY (user_id)
http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/dbd5b982/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql
index 5a0a0b2..ee586bf 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-mysql/schema/upgrade/upgrade-pre-0.9.14.sql
@@ -39,6 +39,12 @@ ALTER TABLE guacamole_connection_history
ADD COLUMN remote_host VARCHAR(256) DEFAULT NULL;
--
+-- Add covering index for connection history connection and start date
+--
+
+ALTER TABLE guacamole_connection_history ADD KEY (connection_id, start_date);
+
+--
-- User login/logout history
--
@@ -55,6 +61,7 @@ CREATE TABLE guacamole_user_history (
KEY `user_id` (`user_id`),
KEY `start_date` (`start_date`),
KEY `end_date` (`end_date`),
+ KEY `user_start_date` (`user_id`, `start_date`),
CONSTRAINT guacamole_user_history_ibfk_1
FOREIGN KEY (user_id)
http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/dbd5b982/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql
index 4840c91..97780a5 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/001-create-schema.sql
@@ -438,6 +438,9 @@ CREATE INDEX guacamole_connection_history_start_date
CREATE INDEX guacamole_connection_history_end_date
ON guacamole_connection_history(end_date);
+CREATE INDEX guacamole_connection_history_connection_id_start_date
+ ON guacamole_connection_history(connection_id, start_date);
+
--
-- User login/logout history
--
@@ -468,6 +471,9 @@ CREATE INDEX guacamole_user_history_start_date
CREATE INDEX guacamole_user_history_end_date
ON guacamole_user_history(end_date);
+CREATE INDEX guacamole_user_history_user_id_start_date
+ ON guacamole_user_history(user_id, start_date);
+
--
-- User password history
--
http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/dbd5b982/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql
index 39e8e59..534d4dc 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-postgresql/schema/upgrade/upgrade-pre-0.9.14.sql
@@ -39,6 +39,13 @@ ALTER TABLE guacamole_connection_history
ADD COLUMN remote_host VARCHAR(256) DEFAULT NULL;
--
+-- Add covering index for connection history connection and start date
+--
+
+CREATE INDEX guacamole_connection_history_connection_id_start_date
+ ON guacamole_connection_history(connection_id, start_date);
+
+--
-- User login/logout history
--
@@ -67,3 +74,6 @@ CREATE INDEX guacamole_user_history_start_date
CREATE INDEX guacamole_user_history_end_date
ON guacamole_user_history(end_date);
+
+CREATE INDEX guacamole_user_history_user_id_start_date
+ ON guacamole_user_history(user_id, start_date);
http://git-wip-us.apache.org/repos/asf/guacamole-client/blob/dbd5b982/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql
----------------------------------------------------------------------
diff --git a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql
index 19d48e4..060503a 100644
--- a/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql
+++ b/extensions/guacamole-auth-jdbc/modules/guacamole-auth-jdbc-sqlserver/schema/001-create-schema.sql
@@ -502,6 +502,9 @@ CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_start_date]
CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_end_date]
ON [guacamole_connection_history] ([end_date]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_connection_history_connection_id_start_date]
+ ON [guacamole_connection_history] ([connection_id], [start_date]);
GO
--
@@ -533,6 +536,9 @@ CREATE NONCLUSTERED INDEX [IX_guacamole_user_history_start_date]
CREATE NONCLUSTERED INDEX [IX_guacamole_user_history_end_date]
ON [guacamole_user_history] ([end_date]);
+
+CREATE NONCLUSTERED INDEX [IX_guacamole_user_history_user_id_start_date]
+ ON [guacamole_user_history] ([user_id], [start_date]);
GO
--