geminispace.info

commit 097cb2a43b6e252ec4cd2a71e2a9302493641d12
parent 28a8f65d5bf8155789bbceef0b2843bfd36d2b16
Author: Remco <me@rwv.io>
Date:   Fri, 20 Nov 2020 23:37:03 +0100

Speed up get_newest_hosts

Signed-off-by: Natalie Pendragon <natpen@natpen.net>

Diffstat:
M
serve/models.py
 | 
19
+++++++++----------

1 file changed, 9 insertions(+), 10 deletions(-)
diff --git a/serve/models.py b/serve/models.py
@@ -191,16 +191,15 @@ OR p.content_type IN ('application/atom+xml', 'application/rss+xml')
 
     def get_newest_hosts(self):
         newest_hosts_query = Page.raw(
-            """SELECT p.domain, MIN(c.timestamp) AS first_seen
-FROM page as p
-JOIN indexable_crawl AS ic
-ON ic.page_id == p.id
-JOIN crawl AS c
-ON c.page_id == p.id
-GROUP BY p.domain
-ORDER BY first_seen DESC
-LIMIT 10
-"""
+            """
+            SELECT p.domain, MIN(c.timestamp) AS first_seen
+            FROM page AS p
+            INNER JOIN crawl AS c ON c.page_id = p.id
+            WHERE c.status = 20
+            GROUP BY p.domain
+            ORDER BY first_seen DESC
+            LIMIT 10
+            """
         )
         return newest_hosts_query.execute()