sql/*: Add username to peer indexes

The username is available for all MAM queries in question, and adding it
to the indexes can improve the lookup performance significantly.

7 files changed, 21 insertions, 22 deletions

diff --git a/sql/lite.new.sql b/sql/lite.new.sql
index 8e9066eb..ef22fe3d 100644
--- a/sql/lite.new.sql
+++ b/sql/lite.new.sql
@@ -109,9 +109,9 @@ CREATE TABLE archive (
 );
 
 CREATE INDEX i_archive_sh_username_timestamp ON archive (server_host, username, timestamp);
+CREATE INDEX i_archive_sh_username_peer ON archive (server_host, username, peer);
+CREATE INDEX i_archive_sh_username_bare_peer ON archive (server_host, username, bare_peer);
 CREATE INDEX i_archive_sh_timestamp ON archive (server_host, timestamp);
-CREATE INDEX i_archive_sh_peer ON archive (server_host, peer);
-CREATE INDEX i_archive_sh_bare_peer ON archive (server_host, bare_peer);
 
 CREATE TABLE archive_prefs (
     username text NOT NULL,
diff --git a/sql/lite.sql b/sql/lite.sql
index e9d2ac6f..2eecec78 100644
--- a/sql/lite.sql
+++ b/sql/lite.sql
@@ -98,9 +98,9 @@ CREATE TABLE archive (
 );
 
 CREATE INDEX i_username_timestamp ON archive(username, timestamp);
+CREATE INDEX i_archive_username_peer ON archive (username, peer);
+CREATE INDEX i_archive_username_bare_peer ON archive (username, bare_peer);
 CREATE INDEX i_timestamp ON archive(timestamp);
-CREATE INDEX i_peer ON archive(peer);
-CREATE INDEX i_bare_peer ON archive(bare_peer);
 
 CREATE TABLE archive_prefs (
     username text NOT NULL PRIMARY KEY,
diff --git a/sql/mssql.sql b/sql/mssql.sql
index 8a24bd5d..2dbe473c 100644
--- a/sql/mssql.sql
+++ b/sql/mssql.sql
@@ -41,13 +41,13 @@ CREATE TABLE [dbo].[archive] (
 CREATE INDEX [archive_username_timestamp] ON [archive] (username, timestamp)

 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

 

-CREATE INDEX [archive_timestamp] ON [archive] (timestamp)

+CREATE INDEX [archive_username_peer] ON [archive] (username, peer)

 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

 

-CREATE INDEX [archive_peer] ON [archive] (peer)

+CREATE INDEX [archive_username_bare_peer] ON [archive] (username, bare_peer)

 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

 

-CREATE INDEX [archive_bare_peer] ON [archive] (bare_peer)

+CREATE INDEX [archive_timestamp] ON [archive] (timestamp)

 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);

 

 CREATE TABLE [dbo].[archive_prefs] (

diff --git a/sql/mysql.new.sql b/sql/mysql.new.sql
index c155794e..9dafe5bb 100644
--- a/sql/mysql.new.sql
+++ b/sql/mysql.new.sql
@@ -113,10 +113,10 @@ CREATE TABLE archive (
 ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
 CREATE FULLTEXT INDEX i_text ON archive(txt);
-CREATE INDEX i_archive_sh_username_timestamp USING BTREE ON archive(server_host(191), username,timestamp);
+CREATE INDEX i_archive_sh_username_timestamp USING BTREE ON archive(server_host(191), username, timestamp);
+CREATE INDEX i_archive_sh_username_peer USING BTREE ON archive(server_host(191), username, peer);
+CREATE INDEX i_archive_sh_username_bare_peer USING BTREE ON archive(server_host(191), username, bare_peer);
 CREATE INDEX i_archive_sh_timestamp USING BTREE ON archive(server_host(191), timestamp);
-CREATE INDEX i_archive_sh_peer USING BTREE ON archive(server_host(191), peer);
-CREATE INDEX i_archive_sh_bare_peer USING BTREE ON archive(server_host(191), bare_peer);
 
 CREATE TABLE archive_prefs (
     username varchar(191) NOT NULL,
diff --git a/sql/mysql.sql b/sql/mysql.sql
index 3e34bbb3..64d26fa8 100644
--- a/sql/mysql.sql
+++ b/sql/mysql.sql
@@ -102,10 +102,10 @@ CREATE TABLE archive (
 ) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
 
 CREATE FULLTEXT INDEX i_text ON archive(txt);
-CREATE INDEX i_username_timestamp USING BTREE ON archive(username,timestamp);
+CREATE INDEX i_username_timestamp USING BTREE ON archive(username, timestamp);
+CREATE INDEX i_username_peer USING BTREE ON archive(username, peer);
+CREATE INDEX i_username_bare_peer USING BTREE ON archive(username, bare_peer);
 CREATE INDEX i_timestamp USING BTREE ON archive(timestamp);
-CREATE INDEX i_peer USING BTREE ON archive(peer);
-CREATE INDEX i_bare_peer USING BTREE ON archive(bare_peer);
 
 CREATE TABLE archive_prefs (
     username varchar(191) NOT NULL PRIMARY KEY,
diff --git a/sql/pg.new.sql b/sql/pg.new.sql
index 3d613a37..66d93423 100644
--- a/sql/pg.new.sql
+++ b/sql/pg.new.sql
@@ -61,15 +61,14 @@
 -- ALTER TABLE spool ALTER COLUMN server_host DROP DEFAULT;
 
 -- ALTER TABLE archive ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
--- DROP INDEX i_username;
 -- DROP INDEX i_username_timestamp;
+-- DROP INDEX i_username_peer;
+-- DROP INDEX i_username_bare_peer;
 -- DROP INDEX i_timestamp;
--- DROP INDEX i_peer;
--- DROP INDEX i_bare_peer;
 -- CREATE INDEX i_archive_sh_username_timestamp ON archive USING btree (server_host, username, timestamp);
+-- CREATE INDEX i_archive_sh_username_peer ON archive USING btree (server_host, username, peer);
+-- CREATE INDEX i_archive_sh_username_bare_peer ON archive USING btree (server_host, username, bare_peer);
 -- CREATE INDEX i_archive_sh_timestamp ON archive USING btree (server_host, timestamp);
--- CREATE INDEX i_archive_sh_peer ON archive USING btree (server_host, peer);
--- CREATE INDEX i_archive_sh_bare_peer ON archive USING btree (server_host, bare_peer);
 -- ALTER TABLE archive ALTER COLUMN server_host DROP DEFAULT;
 
 -- ALTER TABLE archive_prefs ADD COLUMN server_host text NOT NULL DEFAULT '<HOST>';
@@ -265,9 +264,9 @@ CREATE TABLE archive (
 );
 
 CREATE INDEX i_archive_sh_username_timestamp ON archive USING btree (server_host, username, timestamp);
+CREATE INDEX i_archive_sh_username_peer ON archive USING btree (server_host, username, peer);
+CREATE INDEX i_archive_sh_username_bare_peer ON archive USING btree (server_host, username, bare_peer);
 CREATE INDEX i_archive_sh_timestamp ON archive USING btree (server_host, timestamp);
-CREATE INDEX i_archive_sh_peer ON archive USING btree (server_host, peer);
-CREATE INDEX i_archive_sh_bare_peer ON archive USING btree (server_host, bare_peer);
 
 CREATE TABLE archive_prefs (
     username text NOT NULL,
diff --git a/sql/pg.sql b/sql/pg.sql
index 48f4a85d..16456fe0 100644
--- a/sql/pg.sql
+++ b/sql/pg.sql
@@ -102,9 +102,9 @@ CREATE TABLE archive (
 );
 
 CREATE INDEX i_username_timestamp ON archive USING btree (username, timestamp);
+CREATE INDEX i_username_peer ON archive USING btree (username, peer);
+CREATE INDEX i_username_bare_peer ON archive USING btree (username, bare_peer);
 CREATE INDEX i_timestamp ON archive USING btree (timestamp);
-CREATE INDEX i_peer ON archive USING btree (peer);
-CREATE INDEX i_bare_peer ON archive USING btree (bare_peer);
 
 CREATE TABLE archive_prefs (
     username text NOT NULL PRIMARY KEY,