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 <[email protected]> Authored: Mon Dec 11 23:11:57 2017 -0800 Committer: Michael Jumper <[email protected]> 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 --
