On a site with 80,000+ users, the query generated by the friend (flag.friend) View was identified as being part of the problem with some slow page loads. In particular, the two sub-queries in the generated SQL such as:

SELECT DISTINCT(users.uid) AS uid,
   users.name AS users_name,
   flag_friend.uid AS flag_friend_uid
 FROM users users 
 LEFT JOIN flag_friend flag_friend ON users.uid = flag_friend.uid
 WHERE (users.status <> 0)
    AND ((users.uid IN (SELECT f.friend_uid FROM flag_friend f WHERE f.uid = 1)) OR (users.uid IN (SELECT f.uid FROM flag_friend f WHERE f.friend_uid = 1)))
 GROUP BY uid

(The above being generated by the "Current user's friends block" display, with '1' as the argument)

The sub-queries seem to be necessary because dependent on who initiated the friend request and who accepted it, the user being provided as the View argument may be in the f.uid or f.friend_uid column.
After trying to re-work the query, it occurred to me that it may be beneficial in this case to create a new table (let's call it "flag_friend_custom") that would be updated when the module triggers the module_invoke_all() on the 'accepted' and 'removed' events.

If user 4 send a friend request to user 7, and user 7 subsequently accepted, the table would be updated as follows:
uid : friend_uid
4 : 7
7 : 4

The query could then be altered to eliminate the subqueries and the Views argument handler tweaked to do a simple lookup on the flag_friend_custom table. We would know that the user number being used as the argument will always be found in uid column, regardless of initiated the friend request.

Has anybody been down this road and attempted a similar (or found a different) solution?

Comments

timaholt’s picture

timaholt commented

Subscribing. I'm looking into this very query today as it's a frequenter on our slow query logs.

sirkitree’s picture

sirkitree commented

This actually might be even simpler than that. You see, the way flag friend works currently, when I flag you (request your friendship) there is an entry created int the flag table for this. 1, 7. Then, when you flag me back (accept the friend request) there is another entry created in the same table. 7, 1. But then we go on to remove these entries and consolidate them into their own table. So really, at one point, we actually already have a table with the entries 1, 7 and 7, 1. So maybe we just skip the whole process of removing these and creating just one entry in our own table. Or, we still remove these entries and record both to the flag_friend table.

Hrm...

timaholt’s picture

timaholt commented

For reference I've been trying to just rewrite the query without the IN clauses (since that bypasses the index), with the same results, and the resulting query isn't any faster, since it's still not using the primary index:

SELECT  (
users.uid
) AS uid, users.name AS users_name, flag_friend.uid AS flag_friend_uid
FROM users users, flag_friend flag_friend
WHERE users.status <>0
AND (
users.uid = flag_friend.uid
OR users.uid = flag_friend.friend_uid
)
AND (
flag_friend.uid =1361408
OR flag_friend.friend_uid =1361408
)
GROUP BY uid

If you went with sirkitree's above approach and had the 1,7 and 7,1 both present in the flag_friend table, the query could then look more like this:

SELECT users.uid AS uid, users.name AS users_name, flag_friend.uid AS flag_friend_uid
FROM users users, flag_friend flag_friend
WHERE users.status <>0
AND users.uid = flag_friend.uid
AND flag_friend.uid =1361408
GROUP BY uid

Which would be preferable for sure. I'm going to start looking into making sirkitree's suggestion happen.

sirkitree’s picture

sirkitree commented
Status: Active » Closed (won't fix)

Closing due to inactivity.