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<Integer> getMyFeed(Connection sql, int uid, int before) {
         ArrayList<Integer> mids = new ArrayList<Integer>(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<? 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 message_id<? AND 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, 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<? ORDER BY message_id DESC LIMIT 20");
+                stmt = sql.prepareStatement("SELECT message_id FROM (SELECT message_id FROM messages_access WHERE user_id=? AND message_id<? UNION SELECT message_id FROM messages WHERE user_id=? AND privacy<-1 AND message_id<?) AS t ORDER BY message_id DESC LIMIT 20");
                 stmt.setInt(1, uid);
                 stmt.setInt(2, before);
+                stmt.setInt(3, uid);
+                stmt.setInt(4, before);
             } else {
-                stmt = sql.prepareStatement("SELECT message_id FROM messages_access WHERE user_id=? ORDER BY message_id DESC LIMIT 20");
+                stmt = sql.prepareStatement("SELECT message_id FROM (SELECT message_id FROM messages_access WHERE user_id=? UNION SELECT message_id FROM messages WHERE user_id=? AND privacy<-1) AS t ORDER BY message_id DESC LIMIT 20");
                 stmt.setInt(1, uid);
+                stmt.setInt(2, uid);
             }
             rs = stmt.executeQuery();
             rs.beforeFirst();
@@ -366,10 +358,10 @@ public class MessagesQueries {
         ResultSet rs = null;
         try {
             if (before > 0) {
-                stmt = sql.prepareStatement("SELECT message_id FROM messages WHERE message_id<? AND 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.message_id<? AND messages.privacy>0 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