summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorPierre Ducroquet <pducroquet@entrouvert.com>2022-05-23 15:42:49 (GMT)
committerPierre Ducroquet <pducroquet@entrouvert.com>2022-05-23 15:42:49 (GMT)
commit7209ea81a13fb130cc9f2d71caeb67b2c514ba48 (patch)
treedf56ae598358ae063def2550b289d97688f8ea7f
parentf2dd0da20929c466b58d61b513b8dbff5d48c77f (diff)
downloaddocbow-wip/49580-sql-performance.zip
docbow-wip/49580-sql-performance.tar.gz
docbow-wip/49580-sql-performance.tar.bz2
sql: optimize the hand written queries (#49580)v1.42wip/49580-sql-performance
-rw-r--r--docbow_project/docbow/sql.py19
1 files changed, 9 insertions, 10 deletions
diff --git a/docbow_project/docbow/sql.py b/docbow_project/docbow/sql.py
index c3d77d9..799e029 100644
--- a/docbow_project/docbow/sql.py
+++ b/docbow_project/docbow/sql.py
@@ -68,11 +68,10 @@ GET_UNSEEN_DOCUMENTS_SQL = '''SELECT d.id
FROM docbow_document AS d
INNER JOIN docbow_mailbox AS mb
ON mb.outbox = %%s AND mb.document_id = d.id AND mb.owner_id IN %s
- LEFT JOIN docbow_seendocument as sd
- ON sd.document_id = d.id AND sd.user_id = %%s
- LEFT JOIN docbow_deleteddocument AS dd ON
- dd.document_id = d.id AND dd.user_id = %%s
- WHERE dd.id IS NULL AND sd.id IS NULL AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
+ WHERE
+ NOT EXISTS(SELECT 1 FROM docbow_deleteddocument dd WHERE dd.document_id = d.id AND dd.user_id = %%s)
+ AND NOT EXISTS(SELECT 1 FROM docbow_seendocument sd WHERE sd.document_id = d.id AND sd.user_id = %%s)
+ AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
GROUP BY d.id, d.date
ORDER BY d.date
'''
@@ -81,9 +80,9 @@ GET_DOCUMENTS_SQL = '''SELECT d.id
FROM docbow_document AS d
INNER JOIN docbow_mailbox AS mb ON
mb.outbox = %%s AND mb.document_id = d.id AND mb.owner_id IN %s
- LEFT JOIN docbow_deleteddocument AS dd ON
- dd.document_id = d.id AND dd.user_id = %%s
- WHERE dd.id IS NULL AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
+ WHERE
+ NOT EXISTS(SELECT 1 FROM docbow_deleteddocument dd WHERE dd.document_id = d.id AND dd.user_id = %%s)
+ AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
GROUP BY d.id, d.date
ORDER BY d.date'''
@@ -93,8 +92,8 @@ GET_TRASH_DOCUMENTS_SQL = '''SELECT d.id
INNER JOIN docbow_mailbox AS mb ON
mb.outbox = %%s AND mb.document_id = d.id AND mb.owner_id IN %s
LEFT JOIN docbow_deleteddocument AS dd ON
- dd.document_id = d.id AND dd.user_id = %%s
- WHERE dd.soft_delete AND (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
+ dd.document_id = d.id AND dd.user_id = %%s AND dd.soft_delete
+ WHERE (d.private = %%s OR (mb.owner_id = %%s AND d.private = %%s))
GROUP BY d.id, d.date
ORDER BY d.date'''