From 6aa13394a12c1931990b91e62210cba72706920d Mon Sep 17 00:00:00 2001 From: fusion32 Date: Mon, 27 Oct 2025 21:44:13 -0300 Subject: adjust character death indexes for common queries --- postgres/schema.sql | 5 +++-- postgres/z-003-character-deaths-indexes.sql | 20 ++++++++++++++++++++ sqlite/schema.sql | 5 +++-- sqlite/z-003-character-deaths-indexes.sql | 17 +++++++++++++++++ 4 files changed, 43 insertions(+), 4 deletions(-) create mode 100644 postgres/z-003-character-deaths-indexes.sql create mode 100644 sqlite/z-003-character-deaths-indexes.sql diff --git a/postgres/schema.sql b/postgres/schema.sql index 08837c5..bce1b35 100644 --- a/postgres/schema.sql +++ b/postgres/schema.sql @@ -109,8 +109,9 @@ CREATE TABLE CharacterDeaths ( Unjustified BOOLEAN NOT NULL, Timestamp TIMESTAMPTZ NOT NULL ); -CREATE INDEX CharacterDeathsCharacterIndex ON CharacterDeaths(CharacterID, Level); -CREATE INDEX CharacterDeathsOffenderIndex ON CharacterDeaths(OffenderID, Unjustified); +CREATE INDEX CharacterDeathsCharacterIndex ON CharacterDeaths(CharacterID, Timestamp); +CREATE INDEX CharacterDeathsOffenderIndex ON CharacterDeaths(OffenderID, Timestamp); +CREATE INDEX CharacterDeathsTimeIndex ON CharacterDeaths(Timestamp); CREATE TABLE Buddies ( WorldID INTEGER NOT NULL, diff --git a/postgres/z-003-character-deaths-indexes.sql b/postgres/z-003-character-deaths-indexes.sql new file mode 100644 index 0000000..1347b72 --- /dev/null +++ b/postgres/z-003-character-deaths-indexes.sql @@ -0,0 +1,20 @@ +-- NOTE(fusion): This file contains index adjustments for the `CharacterDeaths` +-- table. It is not strictly necessary as there are no modified tables but should +-- help with queries such as the most recent deaths/kills from a specific character +-- or overall. +-- It's inside a transaction to avoid errors from leaving the database in some +-- partial state. The changes are already present in the latest `schema.sql`, so +-- trying to apply them on a newly created database will result in errors. +--============================================================================== + +BEGIN; + +DROP INDEX CharacterDeathsCharacterIndex; +DROP INDEX CharacterDeathsOffenderIndex; + +CREATE INDEX CharacterDeathsCharacterIndex ON CharacterDeaths(CharacterID, Timestamp); +CREATE INDEX CharacterDeathsOffenderIndex ON CharacterDeaths(OffenderID, Timestamp); +CREATE INDEX CharacterDeathsTimeIndex ON CharacterDeaths(Timestamp); + +COMMIT; + diff --git a/sqlite/schema.sql b/sqlite/schema.sql index 2ee1a8b..9981976 100644 --- a/sqlite/schema.sql +++ b/sqlite/schema.sql @@ -94,8 +94,9 @@ CREATE TABLE CharacterDeaths ( Unjustified INTEGER NOT NULL, Timestamp INTEGER NOT NULL ); -CREATE INDEX CharacterDeathsCharacterIndex ON CharacterDeaths(CharacterID, Level); -CREATE INDEX CharacterDeathsOffenderIndex ON CharacterDeaths(OffenderID, Unjustified); +CREATE INDEX CharacterDeathsCharacterIndex ON CharacterDeaths(CharacterID, Timestamp); +CREATE INDEX CharacterDeathsOffenderIndex ON CharacterDeaths(OffenderID, Timestamp); +CREATE INDEX CharacterDeathsTimeIndex ON CharacterDeaths(Timestamp); CREATE TABLE Buddies ( WorldID INTEGER NOT NULL, diff --git a/sqlite/z-003-character-deaths-indexes.sql b/sqlite/z-003-character-deaths-indexes.sql new file mode 100644 index 0000000..9f4baf9 --- /dev/null +++ b/sqlite/z-003-character-deaths-indexes.sql @@ -0,0 +1,17 @@ +-- NOTE(fusion): This file contains index adjustments for the `CharacterDeaths` +-- table. It is not strictly necessary as there are no modified tables but should +-- help with queries such as the most recent deaths/kills from a specific character +-- or overall. +-- It can be executed automatically as a patch if placed at `sqlite/patches`. For +-- more details see `sqlite/README.txt`. The changes are already present in the +-- latest `schema.sql`, so trying to apply them on a newly created database will +-- result in errors. +--============================================================================== + +DROP INDEX CharacterDeathsCharacterIndex; +DROP INDEX CharacterDeathsOffenderIndex; + +CREATE INDEX CharacterDeathsCharacterIndex ON CharacterDeaths(CharacterID, Timestamp); +CREATE INDEX CharacterDeathsOffenderIndex ON CharacterDeaths(OffenderID, Timestamp); +CREATE INDEX CharacterDeathsTimeIndex ON CharacterDeaths(Timestamp); + -- cgit v1.2.3