1

Please bare me if my question is too blunt

I am working on a social network where I need to show similar profiles to users

Every profiles has 40 parameters to match with other profiles.

I want to fetch results in a manner that users with maximum similarity with current logged-in user i.e if user has exact same values to other users then they must be shown at top.

After that users with lesser similarities should be shown i.e 39 similarities

I am planning to use 2 tables

[user][table] ->  id, name, email, password, username etc

[user_values][table] -> It will have 40 columns for all 40 parameters and user_id

Can this be done using single query ?

So we will have 40 values of current login user. Whenever they reach a matches page we should be able to hit a query which will fetch users in order to descending match of columns.

I can change database design/structure if needed.

0

I have limited to 10 user_values for this example. Replace the two instances of "1" with the user_id of the user that you are matching profiles to.

SELECT
    user_id,
    @score :=
        (user_values.value1 = cur_user_values.value1) +
        (user_values.value2 = cur_user_values.value2) +
        (user_values.value3 = cur_user_values.value3) +
        (user_values.value4 = cur_user_values.value4) +
        (user_values.value5 = cur_user_values.value5) +
        (user_values.value6 = cur_user_values.value6) +
        (user_values.value7 = cur_user_values.value7) +
        (user_values.value8 = cur_user_values.value8) +
        (user_values.value9 = cur_user_values.value9) +
        (user_values.value10 = cur_user_values.value10)
        AS score
FROM
    user_values JOIN (
        SELECT
            value1,
            value2,
            value3,
            value4,
            value5,
            value6,
            value7,
            value8,
            value9,
            value10
        FROM
            user_values
        WHERE
            user_values.user_id = 1
    ) cur_user_values
WHERE
    user_id != 1
ORDER BY
    score DESC;

This results in a response like so (I was testing with only 5 users):

enter image description here

In my case, score will be a number between 0 and 10. In your case it will be a number between 0 and 40.

  • 1
    That kind of algorithm ("similarity") requires a table scan. Hence it slows down linearly with the size of the table. – Rick James Feb 17 '16 at 6:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.