Kenny Kenny - 5 months ago 20
MySQL Question

Blocking friends sql (super slow sql??)

Hi im having trouble with this sql. Basically it displays statuses that my friends posted, and it filters out the friends that i blocked.

The sql is slooooowwwwwww though.. It takes 2 seconds to process it. What seems to be the problem. (ive listed the tables and some examples of how it looks below the sql) thanks in advance!
The sql....

SELECT from_user_id,content,wall.date, wall.wall_type, users.id, users.displayname
FROM wall

INNER JOIN friendship ON ( wall.from_user_id =friendship.user_a OR wall.from_user_id = friendship.user_b )
INNER JOIN users ON (wall.from_user_id = users.id)WHERE users.id not in (select blocked_id from blocklist where user_id = 1) and (wall.wall_type = 'home' OR wall.wall_type = 'profile' or wall.wall_type = 'topro') AND (
(friendship.user_a = 1 and friendship.user_b = wall.from_user_id)
or
(friendship.user_a = wall.from_user_id and friendship.user_b = 1) or (wall.user_id_of_wall = 1 or type_id = 1 or from_user_id = 1))GROUP BY wall_id ORDER BY date DESC LIMIT 10


(table name: blocklist)

user_id | blocked_id
1 74
1 70
1 94
1 81


(table name:friendhip)

user_a user_b status date
1 93 1 1297323354
1 79 1 1297323409
1 81 1 1297323403
1 82 1 1297323398
1 85 1 1297323389
1 90 1 1297323367
1 89 1 1297323373


(table name:users)

id displayname
1 Kenny Jack
8 Wale Robinson
7 Victor WIlliams
6 Micheal Harris
9 Micheal Boston
10 Yestor Smith


the wall table

wall_id wall_type user_id_of_wall type_id from_user_id content viewed date
5 profile 8 8 8 Just chilling! 0 1296858001


THANKS!!!

Answer

NOT IN is used for fixed values, use NOT EXISTS instead :

SELECT from_user_id,content,wall.date, wall.wall_type, users.id, users.displayname 
            FROM wall

            INNER JOIN friendship ON ( wall.from_user_id =friendship.user_a OR wall.from_user_id = friendship.user_b ) 
            INNER JOIN users ON (wall.from_user_id = users.id)
WHERE not exists (select * from blocklist where users.id = blocked_id AND user_id = 1) and wall.wall_type IN ('home', 'profile', 'topro') AND (
                  (friendship.user_a = 1 and friendship.user_b = wall.from_user_id)
                or  
                    (friendship.user_a = wall.from_user_id and friendship.user_b = 1)    or (wall.user_id_of_wall = 1 or type_id = 1 or from_user_id = 1))
GROUP BY wall_id
ORDER BY date DESC LIMIT 10 

[EDIT] Use IN for fixed values (wall_type)...

Comments