CODEWITHSUNDEEP
mysql
Mark Murphy
Mark Murphy

Reputation: 2874

Mysql query: fastest way to find users followed by or following another user

I have the following tables:

Relationships

id, follower_id, followee_id, status

Users

id, name, email

I want to find all users who are either following or followed by a specific user.

This is what I have so far but it is very slow:

SELECT DISTINCT
    `users`.*
FROM
    `users`
        INNER JOIN
    `relationships` ON ((`users`.`id` = `relationships`.`follower_id`
        AND `relationships`.`followee_id` = 1)
        OR (`users`.`id` = `relationships`.`followee_id`
        AND `relationships`.`follower_id` = 1))
WHERE
    `relationships`.`status` = 'following'
ORDER BY `users`.`id`

What I mean by slow

I have one user who has roughly 600 followers and 600 following and it takes about 5 seconds for this query to run which seems insanely slow for those numbers!

The explain method shows the following:

+----+-------------+---------------+------+-----------------------------------------------------------------------+------+---------+------+------+------------------------------------------------+
| id | select_type | table         | type | possible_keys                                                         | key  | key_len | ref  | rows | Extra                                          |
+----+-------------+---------------+------+-----------------------------------------------------------------------+------+---------+------+------+------------------------------------------------+
|  1 | SIMPLE      | relationships | ALL  | index_relationships_on_followed_id,index_relationships_on_follower_id | NULL | NULL    | NULL |  727 | Using where; Using temporary; Using filesort   |
|  1 | SIMPLE      | users         | ALL  | PRIMARY                                                               | NULL | NULL    | NULL |  767 | Range checked for each record (index map: 0x1) |
+----+-------------+---------------+------+-----------------------------------------------------------------------+------+---------+------+------+------------------------------------------------+

Upvotes: 0

Views: 228

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

Try breaking this into two queries, with a union:

SELECT u.*
FROM `users` u INNER JOIN
     `relationships` r
      ON u.`id` = r.`follower_id` AND r.`followee_id` = 1
WHERE `r.`status` = 'following'
UNION
SELECT u.*
FROM `users` u INNER JOIN
     `relationships` r
      ON u.`id` = r.`followee_id` AND r.`follower_id` = 1
WHERE `r.`status` = 'following'
ORDER BY id;

This may be a case where a more complicated query has better performance. These queries will also benefit from indexes: relationships(status, follower_id, followee_id) and relationships(status, followee_id, follower_id).

Upvotes: 1

Related Questions