CODEWITHSUNDEEP
mysqldatabaseprofilingsimilarity
gkpo
gkpo

Reputation: 2672

Compare similarities between two result sets

I am creating a music website where I would like users to be able to find users who like approximately the same artists as they.

I have a 'like' table that has 2 columns 'id_user', 'id_artist'. Here is an example of how I would like it to work:

User 1 likes:
1, 12
1, 13
1, 14
1, 26
1, 42
1, 44

User 2 likes:
2, 13
2, 14
2, 15
2, 26
2, 42
2, 56

Those 2 users have 4 artists in common. Is there a way, to compare those 2 results sets, to find the most similar people in the database?

My first idea was to concatenate likes in that way: "12,13,14,26,42,44" in a string, and use mysql FULLTEXT scores to compare different strings. That didn't work... don't know why but mysql fulltext only works with text... not with numbers...

Any idea or any clue would be much appreciated.

Upvotes: 3

Views: 2808

Answers (2)

Hammerite
Hammerite

Reputation: 22340

It is possible to join a table to itself. (You need to specify an alias for at least one of the two "copies" of the table, so that your query is not ambiguous.)

So given two users, you can find the "likes" they have in common by doing a join of the like table to itself. You can also find what proportion of User 1's likes are shared by User 2 by doing a left join and counting both how many results there are and how many are null. Note that this is not a symmetric operation, and you will need to tackle the case where one or both of the numbers is 0.

When you say you want to "find the most similar people in the database": you could do this for every pair of users, but note that if you have n users then this involves doing n*(n-1)/2 comparisons, which is on the order of n squared. This might be quite a lot of work for your database to do if you have a lot of users.

Upvotes: 0

Michael Robinson
Michael Robinson

Reputation: 29498

Something like this:

SELECT first_user.id_user, second_user.id_user, COUNT(first_user.id_user) AS total_matches

FROM likes AS first_user

JOIN likes AS second_user
ON second_user.id_artist = first_user.id_artist
AND second_user.id_user != first_user.id_user

GROUP BY first_user.id_user, second_user.id_user

ORDER BY total_matches DESC

LIMIT 1

Note that this isn't very efficient. One way to work around this is to make a 'cache table' containing the output of this query with the LIMIT 1 portion removed. Add some relevant indexes and do query this cache table. You could set a cron job to update this table periodically.

Example:

CREATE TABLE IF NOT EXISTS `likes` (
  `id_user` varchar(50) DEFAULT NULL,
  `id_artist` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `likes` (`id_user`, `id_artist`) VALUES ('8', '39'), ('8', '37'), ('4', '37'), ('8', '24'), ('8', '7'), ('4', '28'), ('8', '28'), ('4', '27'), ('4', '11'), ('8', '49'), ('4', '7'), ('4', '40'), ('4', '29'), ('8', '22'), ('4', '29'), ('8', '11'), ('8', '28'), ('4', '7'), ('4', '31'), ('8', '42'), ('8', '25'), ('4', '25'), ('4', '17'), ('4', '32'), ('4', '46'), ('4', '19'), ('8', '34'), ('3', '32'), ('4', '21')

+---------+---------+---------------+
| id_user | id_user | total_matches |
+---------+---------+---------------+
| 8       | 4       |             7 |
+---------+---------+---------------+

Upvotes: 2

Related Questions