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.

7 files changed, 24 insertions, 16 deletions

diff --git a/sql/lite.new.sql b/sql/lite.new.sql
index febef6247..f4b73dcb2 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 18ee5915a..b62295120 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 782e492ce..e05a7b51d 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 a5d03c8c7..dc514becf 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 c4ae52de7..ae4a73312 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 c7d22c66a..b69e6ddfa 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 44bea2e03..4cf4f0cbd 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,