From 864552bd5e4913a674845b2a9d7fe2fbb604439b Mon Sep 17 00:00:00 2001 From: Ugnich Anton Date: Sun, 28 Oct 2012 15:49:50 +0700 Subject: SQL optimization --- src/com/juick/server/MessagesQueries.java | 34 ++++++++++++------------------- 1 file changed, 13 insertions(+), 21 deletions(-) (limited to 'src/com') diff --git a/src/com/juick/server/MessagesQueries.java b/src/com/juick/server/MessagesQueries.java index 80d7a280..87784f63 100644 --- a/src/com/juick/server/MessagesQueries.java +++ b/src/com/juick/server/MessagesQueries.java @@ -236,31 +236,20 @@ public class MessagesQueries { public static ArrayList getMyFeed(Connection sql, int uid, int before) { ArrayList mids = new ArrayList(20); - /* - my $sql="SELECT DISTINCT t.message_id FROM ("; - $sql.="SELECT STRAIGHT_JOIN message_id FROM subscr_users INNER JOIN messages ON subscr_users.user_id=messages.user_id WHERE privacy>=0 AND subscr_users.suser_id=$uid"; - $sql.=" UNION SELECT message_id FROM (subscr_users INNER JOIN messages ON subscr_users.user_id=messages.user_id) INNER JOIN wl_users ON messages.user_id=wl_users.user_id WHERE privacy=-1 AND subscr_users.suser_id=$uid AND wl_users.wl_user_id=$uid"; - $sql.=" UNION SELECT message_id FROM messages WHERE user_id=$uid"; - $sql.=" UNION SELECT message_id FROM subscr_messages WHERE suser_id=$uid"; - $sql.=" UNION SELECT message_id FROM favorites WHERE user_id=$uid"; - $sql.=" UNION SELECT message_id FROM favorites INNER JOIN subscr_users ON (subscr_users.suser_id=$uid AND favorites.user_id=subscr_users.user_id)"; - $sql.=") AS t LEFT JOIN messages_tags USING(message_id) WHERE (tag_id NOT IN (SELECT tag_id FROM bl_tags WHERE user_id=$uid) OR tag_id IS NULL)"; - if($before_mid>0) { - $sql.=" AND message_id<$before_mid"; - } - $sql.=" ORDER BY t.message_id DESC LIMIT 20"; - - */ PreparedStatement stmt = null; ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM feed WHERE user_id=? AND message_id=0 OR (privacy=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?)) AND message_id NOT IN (SELECT message_id FROM messages_tags WHERE tag_id IN (SELECT tag_id FROM bl_tags WHERE user_id=?)) ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, uid); stmt.setInt(2, before); + stmt.setInt(3, uid); + stmt.setInt(4, uid); } else { - stmt = sql.prepareStatement("SELECT message_id FROM feed WHERE user_id=? ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT message_id FROM messages INNER JOIN subscr_users ON (subscr_users.suser_id=? AND subscr_users.user_id=messages.user_id) WHERE privacy>=0 OR (privacy=-1 AND messages.user_id IN (SELECT user_id FROM wl_users WHERE wl_user_id=?)) AND message_id NOT IN (SELECT message_id FROM messages_tags WHERE tag_id IN (SELECT tag_id FROM bl_tags WHERE user_id=?)) ORDER BY message_id DESC LIMIT 20"); stmt.setInt(1, uid); + stmt.setInt(2, uid); + stmt.setInt(3, uid); } rs = stmt.executeQuery(); rs.beforeFirst(); @@ -282,12 +271,15 @@ public class MessagesQueries { ResultSet rs = null; try { if (before > 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages_access WHERE user_id=? AND message_id 0) { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id0 AND messages.message_id IN (SELECT message_id FROM favorites WHERE user_id=11574) ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages INNER JOIN favorites ON (favorites.user_id=11574 AND favorites.message_id=messages.message_id) WHERE messages.message_id0 ORDER BY messages.message_id DESC LIMIT 20"); stmt.setInt(1, before); } else { - stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE privacy>0 AND messages.message_id IN (SELECT message_id FROM favorites WHERE user_id=11574) ORDER BY message_id DESC LIMIT 20"); + stmt = sql.prepareStatement("SELECT messages.message_id FROM messages INNER JOIN favorites ON (favorites.user_id=11574 AND favorites.message_id=messages.message_id) WHERE messages.privacy>0 ORDER BY messages.message_id DESC LIMIT 20"); } rs = stmt.executeQuery(); rs.beforeFirst(); -- cgit v1.2.3