aboutsummaryrefslogtreecommitdiff
path: root/src
diff options
context:
space:
mode:
authorGravatar Ugnich Anton2012-10-28 15:49:50 +0700
committerGravatar Ugnich Anton2012-10-28 15:49:50 +0700
commit864552bd5e4913a674845b2a9d7fe2fbb604439b (patch)
treea9853c40fe174507f097d45ef265c2a172ecb687 /src
parent4d77c09e6d382548edae2509ccbd56655cbe3737 (diff)
SQL optimization
Diffstat (limited to 'src')
-rw-r--r--src/com/juick/server/MessagesQueries.java34
1 files changed, 13 insertions, 21 deletions
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();