cmd/marabot: add indices to SQL database

Signed-off-by: Xe Iaso <me@xeiaso.net>

1 files changed, 48 insertions, 0 deletions

diff --git a/cmd/marabot/schema.sql b/cmd/marabot/schema.sql
index 9ddc789..5c5f045 100644
--- a/cmd/marabot/schema.sql
+++ b/cmd/marabot/schema.sql
@@ -19,6 +19,9 @@ CREATE TABLE IF NOT EXISTS discord_users (
     accent_color INTEGER NOT NULL
 );
 
+CREATE INDEX IF NOT EXISTS discord_users_avatar_url
+  ON discord_users(avatar_url);
+
 CREATE TABLE IF NOT EXISTS discord_messages (
     id TEXT PRIMARY KEY,
     guild_id TEXT NOT NULL,
@@ -30,6 +33,15 @@ CREATE TABLE IF NOT EXISTS discord_messages (
     webhook_id TEXT
 );
 
+CREATE INDEX IF NOT EXISTS discord_messages_author_id
+  ON discord_messages(author_id);
+  
+CREATE INDEX IF NOT EXISTS discord_messages_guild_id
+  ON discord_messages(guild_id);
+  
+CREATE INDEX IF NOT EXISTS discord_messages_channel_id
+  ON discord_messages(channel_id);
+
 CREATE TABLE IF NOT EXISTS discord_webhook_message_info (
     id TEXT PRIMARY KEY,
     name TEXT NOT NULL,
@@ -48,6 +60,12 @@ CREATE TABLE IF NOT EXISTS discord_attachments (
     "size" INTEGER NOT NULL
 );
 
+CREATE INDEX IF NOT EXISTS discord_attachments_message_id
+  ON discord_attachments(message_id);
+
+CREATE INDEX IF NOT EXISTS discord_attachments_url
+  ON discord_attachments(url);
+
 CREATE TABLE IF NOT EXISTS discord_channels (
     id TEXT PRIMARY KEY,
     guild_id TEXT NOT NULL,
@@ -56,6 +74,9 @@ CREATE TABLE IF NOT EXISTS discord_channels (
     nsfw BOOLEAN NOT NULL
 );
 
+CREATE INDEX IF NOT EXISTS discord_channels_guild_id
+  ON discord_channels(guild_id);
+
 CREATE TABLE IF NOT EXISTS discord_guilds (
     id TEXT PRIMARY KEY,
     name TEXT NOT NULL,
@@ -71,6 +92,9 @@ CREATE TABLE IF NOT EXISTS discord_emoji (
     url TEXT NOT NULL
 );
 
+CREATE INDEX IF NOT EXISTS discord_emoji_url
+  ON discord_emoji(url);
+
 CREATE TABLE IF NOT EXISTS irc_messages (
     id SERIAL PRIMARY KEY,
     nick TEXT NOT NULL,
@@ -81,6 +105,9 @@ CREATE TABLE IF NOT EXISTS irc_messages (
     tags TEXT NOT NULL
 );
 
+CREATE INDEX IF NOT EXISTS irc_messages_channel
+  ON irc_messages(channel);
+
 CREATE TABLE IF NOT EXISTS revolt_channels (
     id TEXT PRIMARY KEY,
     server_id TEXT NOT NULL,
@@ -95,6 +122,9 @@ CREATE TABLE IF NOT EXISTS revolt_emoji (
     url TEXT NOT NULL
 );
 
+CREATE INDEX IF NOT EXISTS revolt_emoji_url
+  ON revolt_emoji(url);
+
 CREATE TABLE IF NOT EXISTS revolt_discord_emoji (
     revolt_id TEXT NOT NULL,
     discord_id TEXT NOT NULL,
@@ -115,6 +145,9 @@ CREATE TABLE IF NOT EXISTS revolt_message_masquerade (
     avatar_url TEXT NOT NULL
 );
 
+CREATE INDEX IF NOT EXISTS revolt_message_masquerade_avatar_url
+  ON revolt_message_masquerade(avatar_url);
+
 CREATE TABLE IF NOT EXISTS revolt_servers (
     id TEXT PRIMARY KEY,
     name TEXT NOT NULL,
@@ -128,6 +161,9 @@ CREATE TABLE IF NOT EXISTS revolt_users (
     created_at TEXT NOT NULL
 );
 
+CREATE INDEX IF NOT EXISTS revolt_users_avatar_url
+  ON revolt_users(avatar_url);
+
 CREATE TABLE IF NOT EXISTS revolt_attachments (
     id TEXT PRIMARY KEY,
     tag TEXT NOT NULL,
@@ -140,6 +176,12 @@ CREATE TABLE IF NOT EXISTS revolt_attachments (
     "size" INTEGER NOT NULL
 );
 
+CREATE INDEX IF NOT EXISTS revolt_attachments_message_id
+  ON revolt_attachments(message_id);
+
+CREATE INDEX IF NOT EXISTS revolt_attachments_url
+  ON revolt_attachments(url);
+
 CREATE TABLE IF NOT EXISTS s3_uploads (
     id TEXT PRIMARY KEY, -- sha512 of file contents
     url TEXT NOT NULL,
@@ -148,3 +190,9 @@ CREATE TABLE IF NOT EXISTS s3_uploads (
     created_at TEXT NOT NULL,
     message_id TEXT
 );
+
+CREATE INDEX IF NOT EXISTS s3_uploads_url
+  ON s3_uploads(url);
+
+CREATE INDEX IF NOT EXISTS s3_uploads_message_id
+  ON s3_uploads(message_id);