在两个用户之间找到常见喜欢的最佳方式

Question

I have an app that saves user likes from their fb account and then shows common likes when one user visits another one's profile. There are two ways to do that: 1. Since I already have gotten user1's likes from the database, I can save the IDs in a string like

            $user1_likes="1,2,5,6,10,15,19";

and then use this query to find common likes:

          SELECT name FROM user_like WHERE uid='user2' AND id IN ($user1)

or, I can do this query

        SELECT name FROM user_like WHERE uid='user2' AND id IN (SELECT id FROM user_like WHERE uid='user1')

The problem with the first method is, if user1 has a lot of likes (say 4000), the query might become too big for mysql to handle. The problem with method2 is that mysql should select again what it has already selected.

What do you recommend to handle this situation?

Answer 1

max_allowed_packet has a default of 16 MB (http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html), so your first query with 4'000 likes should be no problem at all. Even 10'000 or 100'000 are no problem.

Try to find out which one is faster if there are 10'000/100'000 likes. If you have proper indizes, the second query shouldn't be a problem either.

Personally, I would choose the second one.