aboutsummaryrefslogtreecommitdiff
path: root/sql
diff options
context:
space:
mode:
Diffstat (limited to 'sql')
-rw-r--r--sql/lite.new.sql1
-rw-r--r--sql/lite.sql1
-rw-r--r--sql/mssql.sql34
-rw-r--r--sql/mysql.new.sql1
-rw-r--r--sql/mysql.sql1
-rw-r--r--sql/pg.new.sql1
-rw-r--r--sql/pg.sql1
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,
generated by cgit v1.2.3 (git 2.25.1) at 2024-12-04 18:51:59 +0000