summary refs log tree commit diff
path: root/synapse/storage/search.py
diff options
context:
space:
mode:
authorErik Johnston <erik@matrix.org>2015-11-12 16:24:32 +0000
committerErik Johnston <erik@matrix.org>2015-11-12 16:24:32 +0000
commit49f1758d740f08512bc62d425fb662e8089a3dc6 (patch)
tree372891054738a6c57831ad11fb81005dffe8cb6a /synapse/storage/search.py
parentFix an issue with ignoring power_level changes on divergent graphs (diff)
parentTrailing whitespace (diff)
downloadsynapse-49f1758d740f08512bc62d425fb662e8089a3dc6.tar.xz
Merge pull request #366 from matrix-org/erikj/search_fix_sqlite_faster
Use a (hopefully) more efficient SQL query for doing recency based room search
Diffstat (limited to 'synapse/storage/search.py')
-rw-r--r--synapse/storage/search.py17
1 files changed, 14 insertions, 3 deletions
diff --git a/synapse/storage/search.py b/synapse/storage/search.py
index 2e88c51ad0..380270b009 100644
--- a/synapse/storage/search.py
+++ b/synapse/storage/search.py
@@ -252,12 +252,23 @@ class SearchStore(BackgroundUpdateStore):
                 " WHERE vector @@ query AND room_id = ?"
             )
         elif isinstance(self.database_engine, Sqlite3Engine):
+            # We use CROSS JOIN here to ensure we use the right indexes.
+            # https://sqlite.org/optoverview.html#crossjoin
+            #
+            # We want to use the full text search index on event_search to
+            # extract all possible matches first, then lookup those matches
+            # in the events table to get the topological ordering. We need
+            # to use the indexes in this order because sqlite refuses to
+            # MATCH unless it uses the full text search index
             sql = (
-                "SELECT rank(matchinfo(event_search)) as rank, room_id, event_id,"
+                "SELECT rank(matchinfo) as rank, room_id, event_id,"
                 " topological_ordering, stream_ordering"
+                " FROM (SELECT key, event_id, matchinfo(event_search) as matchinfo"
                 " FROM event_search"
-                " NATURAL JOIN events"
-                " WHERE value MATCH ? AND room_id = ?"
+                " WHERE value MATCH ?"
+                " )"
+                " CROSS JOIN events USING (event_id)"
+                " WHERE room_id = ?"
             )
         else:
             # This should be unreachable.