I write social. network in PHP and MySQL. Made a table with likes fields id , profile_id , partner_id , date , read .

Now you need to pull out all the users that I liked and they liked me. That is mutual huskies.

So far, I have 2 arrays stretched: an array of users I liked, and an array of users who liked me. And then in the loop I check by the method of sorting whether the likes are mutual.

How to optimize it?

  • Suppose you have ID = 1, your friend has ID = 2, which records will then lie in the likes table in case you like each other? - Mike
  • 1 1 2 2016-05-18 14:55:59 0 - Ivan Tsurkan
  • 2 2 1 2016-05-18 11:20:49 0 - Ivan Tsurkan

2 answers 2

Search for such people for the user ID = 1, if you need all cross-references for all users, you need to remove where .

 select A.partner_id from likes A join likes B on B.partner_id=A.profile_id and A.partner_id=B.profile_id where A.profile_id=1

If the same person can like the other several times (i.e. you have several records in the database with profile = X and partner = Y) then add a distinct after select to get unique users

  • It would be wise to forbid repeated likes using a unique composite index (partner_id, profile_id). And then without any checks like through the INSERT IGNORE operator. - artoodetoo

It is necessary to do a selection from the table twice:

 SELECT * FROM users WHERE partner_id = ? AND profile_id IN ( SELECT partner_id FROM users WHERE profile_id = ? ) AS megusta

Instead of ? substitute your id.

Another option is to consider any like as a potential ratio of two users, and store no more than one entry in the table for any pair:

 uid_A uid_B like_AB like_BA date_AB date_BA seen_AB seen_BA 1 2 1 1 2016-05.. 2016-05.. 0 0

“Agree” that position A is always put on the user with the lower id. Although it is necessary to search on both columns. With indexes, this is not a problem.

If you grow to millions, you can move to nosql by making the composite index uid_A<разделитель>uid_B .