From 1172b452e371ba79fe3e5e2f28e23c7f67735771 Mon Sep 17 00:00:00 2001 From: Tor Andersson Date: Sun, 3 Nov 2024 23:57:05 +0100 Subject: Optimize tournament blacklist query. Only include contacts where both players are in the queue. --- server.js | 11 ++++++++++- 1 file changed, 10 insertions(+), 1 deletion(-) (limited to 'server.js') diff --git a/server.js b/server.js index 4de0fbd..b9bc612 100644 --- a/server.js +++ b/server.js @@ -2764,7 +2764,16 @@ const TM_POOL_LIST_TITLE_FINISHED = SQL(` const TM_POOL_LIST_SEED_ACTIVE = SQL("select * from tm_pool_active_view where seed_id = ?") const TM_POOL_LIST_SEED_FINISHED = SQL("select * from tm_pool_finished_view where seed_id = ?") -const TM_SELECT_QUEUE_BLACKLIST = SQL("select me, you from contacts join tm_queue q on q.user_id=me or q.user_id=you where relation < 0 and seed_id=? and level=?") +const TM_SELECT_QUEUE_BLACKLIST = SQL(` + with qq as ( + select user_id from tm_queue where seed_id=? and level=? + ) + select me, you + from contacts + join qq on qq.user_id = me + where relation < 0 and exists (select 1 from qq where user_id = you) +`) + const TM_SELECT_QUEUE_NAMES = SQL("select user_id, name, level from tm_queue join users using(user_id) where seed_id=? and level=? order by time") const TM_SELECT_QUEUE = SQL("select user_id from tm_queue where seed_id=? and level=? order by time desc").pluck() const TM_DELETE_QUEUE = SQL("delete from tm_queue where user_id=? and seed_id=? and level=?") -- cgit v1.2.3