How can I optimize this query to execute faster?

Question

This is for the friends module on my site, where users can make friends with each other. These are stored in a "friends:" table, with person who initiated the friendship being the friendship_inviter and the person on the approve/deny end of things is the friendhsip_accepter

SELECT user_id, user_name, user_gender
FROM friends
LEFT JOIN users
    ON (   users.user_id = friends.friendship_inviter 
        OR users.user_id = friends.friendship_accepter)
WHERE (friendship_inviter = '125' OR friendship_accepter = '125') 
AND    user_id !='125' 
AND    friendship_level = 1;

This does a full table scan, and even though the tables are not large (15,000 users, 3000 friendships), it takes 1-1.5 seconds on average.

How can I output this list of current friends in a way that would be less taxing n the server?

Answer 1

Add indexes to the columns/combinations used in the WHERE clause and the JOIN.

Answer 2

Make sure you have the user_id, friendship_accepter, friendship_inviter and friendship_level indexed.

Also is that time you are posting just the raw query time or does it include the time to output the results? If it also includes the output it may not be the query that is slow but the output.

Another thing that could be slowing down the results is if it is over a network that is slow (ie. the Internet). The slow time may be because of this and not because of the query.

Answer 3

SELECT user_id, user_name, user_gender
FROM friends
LEFT JOIN users
    ON users.user_id = friends.friendship_accepter
WHERE  friendship_accepter = '125' 
AND    user_id !='125' 
AND    friendship_level = 1
UNION 
SELECT user_id, user_name, user_gender
FROM friends
LEFT JOIN users
    ON users.user_id = friends.friendship_inviter
WHERE friendship_inviter = '125'  
AND    user_id !='125' 
AND    friendship_level = 1;

Of course, it looks like you aren't selecting anything from the "friends" table, AND you have extra columns in your WHERE clause, so I would actually write INNER JOINs instead, which are equivalent to the LEFT JOINs in the case you posted:

SELECT user_id, user_name, user_gender
FROM friends
INNER JOIN users
    ON users.user_id = friends.friendship_accepter
WHERE  friendship_accepter = '125' 
AND    user_id !='125' 
AND    friendship_level = 1
UNION 
SELECT user_id, user_name, user_gender
FROM friends
INNER JOIN users
    ON users.user_id = friends.friendship_inviter
WHERE friendship_inviter = '125'  
AND    user_id !='125' 
AND    friendship_level = 1;

Answer 4

Part of the problem might be that friendship_level is of low cardinality. Sometimes, on low-cardinality columns, the query optimizer will decide to do a table scan anyway.

Do an EXPLAIN, and make sure that your other conditions are executing FIRST, before the friendship_level condition is considered in the query. A scan of the records remaining after the first two conditions are satisfied would take far less time.

Answer 5

I do something very very similar on my social network site however mine is a little different, I insert 2 records for each friendship, this allows me to show a user;

pending friend request sent friend request and confirmed friends

I't also eliminates the use of UNION, something to consider. I currently have around 50,000 users and over 1 millions rows in the friend table and this works pretty good for me