From f86055378d6337c0e0b1555067f76e62f9265c8c Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Pawe=C5=82=20Chmielowski?= Date: Fri, 18 Feb 2022 14:01:22 +0100 Subject: Optimize room_unused_* commands Previously to check if hibernated room was old enough we had to fetch info about all rooms from database. Now we repurpose created_at field in sql to store that info, that allow us to have more efficient query just for it. --- sql/lite.new.sql | 1 + sql/lite.sql | 1 + sql/mssql.sql | 34 ++++++++++++++++++---------------- sql/mysql.new.sql | 1 + sql/mysql.sql | 1 + sql/pg.new.sql | 1 + sql/pg.sql | 1 + 7 files changed, 24 insertions(+), 16 deletions(-) (limited to 'sql') diff --git a/sql/lite.new.sql b/sql/lite.new.sql index febef624..f4b73dcb 100644 --- a/sql/lite.new.sql +++ b/sql/lite.new.sql @@ -285,6 +285,7 @@ CREATE TABLE muc_room ( ); CREATE UNIQUE INDEX i_muc_room_name_host ON muc_room (name, host); +CREATE INDEX i_muc_room_host_created_at ON muc_room USING btree (host, created_at); CREATE TABLE muc_registered ( jid text NOT NULL, diff --git a/sql/lite.sql b/sql/lite.sql index 18ee5915..b6229512 100644 --- a/sql/lite.sql +++ b/sql/lite.sql @@ -260,6 +260,7 @@ CREATE TABLE muc_room ( ); CREATE UNIQUE INDEX i_muc_room_name_host ON muc_room (name, host); +CREATE INDEX i_muc_room_host_created_at ON muc_room USING btree (host, created_at); CREATE TABLE muc_registered ( jid text NOT NULL, diff --git a/sql/mssql.sql b/sql/mssql.sql index 782e492c..e05a7b51 100644 --- a/sql/mssql.sql +++ b/sql/mssql.sql @@ -32,7 +32,7 @@ CREATE TABLE [dbo].[archive] ( [kind] [varchar] (10) NULL, [nick] [varchar] (250) NULL, [created_at] [datetime] NOT NULL DEFAULT GETDATE(), - CONSTRAINT [archive_PK] PRIMARY KEY CLUSTERED + CONSTRAINT [archive_PK] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -56,7 +56,7 @@ CREATE TABLE [dbo].[archive_prefs] ( [always] [text] NOT NULL, [never] [text] NOT NULL, [created_at] [datetime] NOT NULL DEFAULT GETDATE(), - CONSTRAINT [archive_prefs_PRIMARY] PRIMARY KEY CLUSTERED + CONSTRAINT [archive_prefs_PRIMARY] PRIMARY KEY CLUSTERED ( [username] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -76,7 +76,7 @@ CREATE TABLE [dbo].[last] ( [username] [varchar] (250) NOT NULL, [seconds] [text] NOT NULL, [state] [text] NOT NULL, - CONSTRAINT [last_PRIMARY] PRIMARY KEY CLUSTERED + CONSTRAINT [last_PRIMARY] PRIMARY KEY CLUSTERED ( [username] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -86,7 +86,7 @@ CREATE TABLE [dbo].[motd] ( [username] [varchar] (250) NOT NULL, [xml] [text] NULL, [created_at] [datetime] NOT NULL DEFAULT GETDATE(), - CONSTRAINT [motd_PRIMARY] PRIMARY KEY CLUSTERED + CONSTRAINT [motd_PRIMARY] PRIMARY KEY CLUSTERED ( [username] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -114,6 +114,8 @@ CREATE TABLE [dbo].[muc_room] ( CREATE UNIQUE CLUSTERED INDEX [muc_room_name_host] ON [muc_room] (name, host) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); +CREATE INDEX [muc_room_host_created_at] ON [muc_registered] (host, nick) + WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON); CREATE TABLE [dbo].[muc_online_room] ( [name] [varchar] (250) NOT NULL, @@ -155,7 +157,7 @@ CREATE INDEX [muc_room_subscribers_jid] ON [muc_room_subscribers] (jid); CREATE TABLE [dbo].[privacy_default_list] ( [username] [varchar] (250) NOT NULL, [name] [varchar] (250) NOT NULL, - CONSTRAINT [privacy_default_list_PRIMARY] PRIMARY KEY CLUSTERED + CONSTRAINT [privacy_default_list_PRIMARY] PRIMARY KEY CLUSTERED ( [username] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -166,7 +168,7 @@ CREATE TABLE [dbo].[privacy_list] ( [name] [varchar] (250) NOT NULL, [id] [bigint] IDENTITY(1,1) NOT NULL, [created_at] [datetime] NOT NULL DEFAULT GETDATE(), - CONSTRAINT [privacy_list_PK] PRIMARY KEY CLUSTERED + CONSTRAINT [privacy_list_PK] PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -245,7 +247,7 @@ CREATE TABLE [dbo].[pubsub_state] ( [affiliation] [char] (1) NOT NULL, [subscriptions] [text] NOT NULL DEFAULT '', [stateid] [bigint] IDENTITY(1,1) NOT NULL, - CONSTRAINT [pubsub_state_PRIMARY] PRIMARY KEY CLUSTERED + CONSTRAINT [pubsub_state_PRIMARY] PRIMARY KEY CLUSTERED ( [stateid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -272,7 +274,7 @@ CREATE TABLE [dbo].[pubsub_node] ( [parent] [varchar] (255) NOT NULL DEFAULT '', [plugin] [text] NOT NULL, [nodeid] [bigint] IDENTITY(1,1) NOT NULL, - CONSTRAINT [pubsub_node_PRIMARY] PRIMARY KEY CLUSTERED + CONSTRAINT [pubsub_node_PRIMARY] PRIMARY KEY CLUSTERED ( [nodeid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -287,7 +289,7 @@ WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW CREATE TABLE [dbo].[roster_version] ( [username] [varchar] (250) NOT NULL, [version] [text] NOT NULL, - CONSTRAINT [roster_version_PRIMARY] PRIMARY KEY CLUSTERED + CONSTRAINT [roster_version_PRIMARY] PRIMARY KEY CLUSTERED ( [username] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -348,7 +350,7 @@ CREATE TABLE [dbo].[spool] ( [xml] [text] NOT NULL, [seq] [bigint] IDENTITY(1,1) NOT NULL, [created_at] [datetime] NOT NULL DEFAULT GETDATE(), - CONSTRAINT [spool_PK] PRIMARY KEY CLUSTERED + CONSTRAINT [spool_PK] PRIMARY KEY CLUSTERED ( [seq] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -365,7 +367,7 @@ CREATE TABLE [dbo].[sr_group] ( [name] [varchar] (250) NOT NULL, [opts] [text] NOT NULL, [created_at] [datetime] NOT NULL DEFAULT GETDATE(), - CONSTRAINT [sr_group_PRIMARY] PRIMARY KEY CLUSTERED + CONSTRAINT [sr_group_PRIMARY] PRIMARY KEY CLUSTERED ( [name] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -393,7 +395,7 @@ CREATE TABLE [dbo].[users] ( [salt] [text] NOT NULL DEFAULT '', [iterationcount] [smallint] NOT NULL DEFAULT 0, [created_at] [datetime] NOT NULL DEFAULT GETDATE(), - CONSTRAINT [users_PRIMARY] PRIMARY KEY CLUSTERED + CONSTRAINT [users_PRIMARY] PRIMARY KEY CLUSTERED ( [username] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -403,7 +405,7 @@ CREATE TABLE [dbo].[vcard] ( [username] [varchar] (250) NOT NULL, [vcard] [text] NOT NULL, [created_at] [datetime] NOT NULL DEFAULT GETDATE(), - CONSTRAINT [vcard_PRIMARY] PRIMARY KEY CLUSTERED + CONSTRAINT [vcard_PRIMARY] PRIMARY KEY CLUSTERED ( [username] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -434,7 +436,7 @@ CREATE TABLE [dbo].[vcard_search] ( [lorgname] [varchar] (250) NOT NULL, [orgunit] [text] NOT NULL, [lorgunit] [varchar] (250) NOT NULL, - CONSTRAINT [vcard_search_PRIMARY] PRIMARY KEY CLUSTERED + CONSTRAINT [vcard_search_PRIMARY] PRIMARY KEY CLUSTERED ( [lusername] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -502,7 +504,7 @@ CREATE TABLE [dbo].[oauth_token] ( [jid] [text] NOT NULL, [scope] [text] NOT NULL, [expire] [bigint] NOT NULL, - CONSTRAINT [oauth_token_PRIMARY] PRIMARY KEY CLUSTERED + CONSTRAINT [oauth_token_PRIMARY] PRIMARY KEY CLUSTERED ( [token] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) @@ -526,7 +528,7 @@ CREATE TABLE [dbo].[bosh] ( [sid] [varchar] (255) NOT NULL, [node] [varchar] (255) NOT NULL, [pid] [varchar](100) NOT NULL - CONSTRAINT [bosh_PRIMARY] PRIMARY KEY CLUSTERED + CONSTRAINT [bosh_PRIMARY] PRIMARY KEY CLUSTERED ( [sid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) diff --git a/sql/mysql.new.sql b/sql/mysql.new.sql index a5d03c8c..dc514bec 100644 --- a/sql/mysql.new.sql +++ b/sql/mysql.new.sql @@ -301,6 +301,7 @@ CREATE TABLE muc_room ( ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE UNIQUE INDEX i_muc_room_name_host USING BTREE ON muc_room(name(75), host(75)); +CREATE INDEX i_muc_room_host_created_at ON muc_room(host(75), created_at); CREATE TABLE muc_registered ( jid text NOT NULL, diff --git a/sql/mysql.sql b/sql/mysql.sql index c4ae52de..ae4a7331 100644 --- a/sql/mysql.sql +++ b/sql/mysql.sql @@ -276,6 +276,7 @@ CREATE TABLE muc_room ( ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE UNIQUE INDEX i_muc_room_name_host USING BTREE ON muc_room(name(75), host(75)); +CREATE INDEX i_muc_room_host_created_at ON muc_room(host(75), created_at); CREATE TABLE muc_registered ( jid text NOT NULL, diff --git a/sql/pg.new.sql b/sql/pg.new.sql index c7d22c66..b69e6ddf 100644 --- a/sql/pg.new.sql +++ b/sql/pg.new.sql @@ -450,6 +450,7 @@ CREATE TABLE muc_room ( ); CREATE UNIQUE INDEX i_muc_room_name_host ON muc_room USING btree (name, host); +CREATE INDEX i_muc_room_host_created_at ON muc_room USING btree (host, created_at); CREATE TABLE muc_registered ( jid text NOT NULL, diff --git a/sql/pg.sql b/sql/pg.sql index 44bea2e0..4cf4f0cb 100644 --- a/sql/pg.sql +++ b/sql/pg.sql @@ -278,6 +278,7 @@ CREATE TABLE muc_room ( ); CREATE UNIQUE INDEX i_muc_room_name_host ON muc_room USING btree (name, host); +CREATE INDEX i_muc_room_host_created_at ON muc_room USING btree (host, created_at); CREATE TABLE muc_registered ( jid text NOT NULL, -- cgit v1.2.3