diff options
Diffstat (limited to 'sql')
| -rw-r--r-- | sql/lite.new.sql | 4 | ||||
| -rw-r--r-- | sql/lite.sql | 4 | ||||
| -rw-r--r-- | sql/mssql.sql | 6 | ||||
| -rw-r--r-- | sql/mysql.new.sql | 6 | ||||
| -rw-r--r-- | sql/mysql.sql | 6 | ||||
| -rw-r--r-- | sql/pg.new.sql | 13 | ||||
| -rw-r--r-- | sql/pg.sql | 4 |
7 files changed, 21 insertions, 22 deletions
diff --git a/sql/lite.new.sql b/sql/lite.new.sql index 8e9066eb9..ef22fe3d7 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 e9d2ac6f2..2eecec78a 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 8a24bd5d5..2dbe473c0 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 c155794ee..9dafe5bb2 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 3e34bbb32..64d26fa87 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 3d613a37b..66d934236 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 48f4a85d4..16456fe0f 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, |