summary refs log tree commit diff
diff options
context:
space:
mode:
authorJune McEnroe <june@causal.agency>2020-07-07 13:45:25 -0400
committerJune McEnroe <june@causal.agency>2020-07-07 13:45:25 -0400
commit0c6b86f62fa7ba61b732c0f2e91f10b0c6808896 (patch)
tree77281121850fcf2aed6111da1923df59cd2155be
parentUse >= and < on events.time for -D (diff)
downloadlitterbox-0c6b86f62fa7ba61b732c0f2e91f10b0c6808896.tar.gz
litterbox-0c6b86f62fa7ba61b732c0f2e91f10b0c6808896.zip
Create index on events.time
This vastly improves the speed of scoop -a, -b and -D.
-rw-r--r--database.h10
1 files changed, 8 insertions, 2 deletions
diff --git a/database.h b/database.h
index a701a9d..a0cba04 100644
--- a/database.h
+++ b/database.h
@@ -42,7 +42,7 @@
 
 #define DATABASE_PATH "litterbox/litterbox.sqlite"
 
-enum { DatabaseVersion = 2 };
+enum { DatabaseVersion = 3 };
 
 #define ENUM_TYPE \
 	X(Privmsg, "privmsg") \
@@ -282,6 +282,7 @@ static const char *InitSQL = SQL(
 		target TEXT,
 		message TEXT
 	);
+	CREATE INDEX eventsTime ON events (time);
 
 	CREATE VIEW text (
 		event, network, channel, query, nick, user, target, message
@@ -322,7 +323,7 @@ static const char *InitSQL = SQL(
 		UNIQUE (host, port)
 	);
 
-	PRAGMA user_version = 2;
+	PRAGMA user_version = 3;
 
 	COMMIT TRANSACTION;
 );
@@ -357,6 +358,11 @@ static const char *MigrationSQL[] = {
 		);
 		PRAGMA user_version = 2;
 	),
+
+	SQL(
+		CREATE INDEX eventsTime ON events (time);
+		PRAGMA user_version = 3;
+	),
 };
 
 static inline void dbMigrate(void) {