From 216b85b053d091306e3311a21f5b050f70a75130 Mon Sep 17 00:00:00 2001
From: Eugen Rochko <eugen@zeonfederated.com>
Date: Mon, 14 Dec 2020 09:06:34 +0100
Subject: Fix performance on instances list in admin UI (#15282)

- Reduce duplicate queries
- Remove n+1 queries
- Add accounts count to detailed view
- Add separate action log entry for updating existing domain blocks
---
 db/views/instances_v01.sql | 17 +++++++++++++++++
 1 file changed, 17 insertions(+)
 create mode 100644 db/views/instances_v01.sql

(limited to 'db/views/instances_v01.sql')

diff --git a/db/views/instances_v01.sql b/db/views/instances_v01.sql
new file mode 100644
index 000000000..94acd61a1
--- /dev/null
+++ b/db/views/instances_v01.sql
@@ -0,0 +1,17 @@
+WITH domain_counts(domain, accounts_count)
+AS (
+  SELECT domain, COUNT(*) as accounts_count
+  FROM accounts
+  WHERE domain IS NOT NULL
+  GROUP BY domain
+)
+SELECT domain, accounts_count
+FROM domain_counts
+UNION
+SELECT domain_blocks.domain, COALESCE(domain_counts.accounts_count, 0)
+FROM domain_blocks
+LEFT OUTER JOIN domain_counts ON domain_counts.domain = domain_blocks.domain
+UNION
+SELECT domain_allows.domain, COALESCE(domain_counts.accounts_count, 0)
+FROM domain_allows
+LEFT OUTER JOIN domain_counts ON domain_counts.domain = domain_allows.domain
-- 
cgit 

