From 9f514915afe813cfb531748e252479b6332e5d76 Mon Sep 17 00:00:00 2001
From: Erik Johnston <erik@matrix.org>
Date: Fri, 22 Aug 2014 18:02:45 +0100
Subject: Add indices to schema

---
 synapse/storage/schema/im.sql | 20 ++++++++++++++++++++
 1 file changed, 20 insertions(+)

(limited to 'synapse/storage/schema/im.sql')

diff --git a/synapse/storage/schema/im.sql b/synapse/storage/schema/im.sql
index 39a1ed703e..7f4e758892 100644
--- a/synapse/storage/schema/im.sql
+++ b/synapse/storage/schema/im.sql
@@ -26,6 +26,11 @@ CREATE TABLE IF NOT EXISTS events(
     CONSTRAINT ev_uniq UNIQUE (event_id)
 );
 
+CREATE INDEX IF NOT EXISTS events_event_id ON events (event_id);
+CREATE INDEX IF NOT EXISTS events_stream_ordering ON events (stream_ordering);
+CREATE INDEX IF NOT EXISTS events_topological_ordering ON events (topological_ordering);
+CREATE INDEX IF NOT EXISTS events_room_id ON events (room_id);
+
 CREATE TABLE IF NOT EXISTS state_events(
     event_id TEXT NOT NULL,
     room_id TEXT NOT NULL,
@@ -34,6 +39,12 @@ CREATE TABLE IF NOT EXISTS state_events(
     prev_state TEXT
 );
 
+CREATE UNIQUE INDEX IF NOT EXISTS state_events_event_id ON state_events (event_id);
+CREATE INDEX IF NOT EXISTS state_events_room_id ON state_events (room_id);
+CREATE INDEX IF NOT EXISTS state_events_type ON state_events (type);
+CREATE INDEX IF NOT EXISTS state_events_state_key ON state_events (state_key);
+
+
 CREATE TABLE IF NOT EXISTS current_state_events(
     event_id TEXT NOT NULL,
     room_id TEXT NOT NULL,
@@ -42,6 +53,11 @@ CREATE TABLE IF NOT EXISTS current_state_events(
     CONSTRAINT curr_uniq UNIQUE (room_id, type, state_key) ON CONFLICT REPLACE
 );
 
+CREATE INDEX IF NOT EXISTS curr_events_event_id ON current_state_events (event_id);
+CREATE INDEX IF NOT EXISTS current_state_events_room_id ON current_state_events (room_id);
+CREATE INDEX IF NOT EXISTS current_state_events_type ON current_state_events (type);
+CREATE INDEX IF NOT EXISTS current_state_events_state_key ON current_state_events (state_key);
+
 CREATE TABLE IF NOT EXISTS room_memberships(
     event_id TEXT NOT NULL,
     user_id TEXT NOT NULL,
@@ -50,6 +66,10 @@ CREATE TABLE IF NOT EXISTS room_memberships(
     membership TEXT NOT NULL
 );
 
+CREATE INDEX IF NOT EXISTS room_memberships_event_id ON room_memberships (event_id);
+CREATE INDEX IF NOT EXISTS room_memberships_room_id ON room_memberships (room_id);
+CREATE INDEX IF NOT EXISTS room_memberships_user_id ON room_memberships (user_id);
+
 CREATE TABLE IF NOT EXISTS feedback(
     event_id TEXT NOT NULL,
     feedback_type TEXT,
-- 
cgit 1.4.1


From 9d86c8c7a68fdbfa49df8bb027a112158df40bb8 Mon Sep 17 00:00:00 2001
From: Erik Johnston <lightportal@gmail.com>
Date: Sun, 24 Aug 2014 11:28:03 +0100
Subject: Add a unique constraint on the room hosts table

---
 synapse/storage/schema/im.sql | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

(limited to 'synapse/storage/schema/im.sql')

diff --git a/synapse/storage/schema/im.sql b/synapse/storage/schema/im.sql
index 7f4e758892..e92f21ef3b 100644
--- a/synapse/storage/schema/im.sql
+++ b/synapse/storage/schema/im.sql
@@ -98,5 +98,6 @@ CREATE TABLE IF NOT EXISTS rooms(
 
 CREATE TABLE IF NOT EXISTS room_hosts(
     room_id TEXT NOT NULL,
-    host TEXT NOT NULL
+    host TEXT NOT NULL,
+    CONSTRAINT room_hosts_uniq UNIQUE (room_id, host) ON CONFLICT IGNORE
 );
-- 
cgit 1.4.1

