adjust character death indexes for common queries

2 files changed, 20 insertions, 2 deletions

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);
+