From 9f514915afe813cfb531748e252479b6332e5d76 Mon Sep 17 00:00:00 2001 From: Erik Johnston 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') 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