From ce3a0002bec63ff1f01730d6b71ed4f213cceef3 Mon Sep 17 00:00:00 2001 From: Vitaly Takmazov Date: Fri, 3 Feb 2023 23:14:46 +0300 Subject: Improve top query speed --- src/main/java/com/juick/service/MessagesServiceImpl.java | 9 ++++++--- 1 file changed, 6 insertions(+), 3 deletions(-) (limited to 'src/main/java/com/juick/service/MessagesServiceImpl.java') 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 -- cgit v1.2.3