aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
authorGravatar Vitaly Takmazov2023-02-03 23:14:46 +0300
committerGravatar Vitaly Takmazov2023-02-03 23:14:46 +0300
commitce3a0002bec63ff1f01730d6b71ed4f213cceef3 (patch)
tree3be7c473caa8540f0d520b6f54298ab674f8b896 /src
parentc425c26088f599b26308b36b5950560763753f5a (diff)
Improve top query speed
Diffstat (limited to 'src')
-rw-r--r--src/main/java/com/juick/service/MessagesServiceImpl.java9
1 files changed, 6 insertions, 3 deletions
diff --git a/src/main/java/com/juick/service/MessagesServiceImpl.java b/src/main/java/com/juick/service/MessagesServiceImpl.java
index 55a44bb9..d5583641 100644
--- a/src/main/java/com/juick/service/MessagesServiceImpl.java
+++ b/src/main/java/com/juick/service/MessagesServiceImpl.java
@@ -1041,15 +1041,18 @@ public class MessagesServiceImpl extends BaseJdbcService implements MessagesServ
LEFT JOIN favorites ON favorites.message_id = messages.message_id
LEFT JOIN messages_tags ON messages_tags.message_id = messages.message_id
LEFT JOIN tags ON messages_tags.tag_id=tags.tag_id
- WHERE COALESCE(tags.notop, 0)=0 AND replies.ts > :before
+ WHERE replies.ts > :before
+ AND NOT EXISTS (SELECT 1 FROM tags WHERE messages_tags.message_id=messages.message_id
+ AND messages_tags.tag_id=tags.tag_id AND tags.notop = 1)
AND NOT EXISTS (SELECT 1 FROM favorites WHERE message_id = messages.message_id
AND user_id = 2) GROUP BY replies.message_id HAVING COUNT(DISTINCT(replies.user_id)) > 5
UNION ALL SELECT favorites.message_id FROM favorites
INNER JOIN messages ON messages.message_id = favorites.message_id
LEFT JOIN messages_tags ON messages_tags.message_id = messages.message_id
LEFT JOIN tags ON messages_tags.tag_id=tags.tag_id
- WHERE COALESCE(tags.notop, 0)=0
- AND favorites.ts > :before
+ WHERE favorites.ts > :before
+ AND NOT EXISTS (SELECT 1 FROM tags WHERE messages_tags.message_id=messages.message_id
+ AND messages_tags.tag_id=tags.tag_id AND tags.notop = 1)
AND NOT EXISTS (SELECT 1 FROM favorites
WHERE message_id = messages.message_id AND user_id = 2)
GROUP BY favorites.message_id HAVING COUNT(DISTINCT favorites.user_id) > 2