Amis communs sql

Question

J'ai vu plusieurs messages de SO sur des amis communs, mais j'ai structuré ma table d'amis dans ma base de données de sorte qu'il n'y ait pas de doublons, par exemple (1,2) et non (2,1).

    Create Table Friends(
      user1_id int, 
      user2_id int
    );

et ensuite une contrainte pour s'assurer que l'id de l'utilisateur 1 est toujours plus petit que l'id de l'utilisateur 2, par exemple 4 < 5.

Mutual friends sql with join (Mysql)

Je vois des suggestions pour trouver des amis communs en utilisant une jointure, c'est donc ce que j'ai mais je pense que c'est faux car si je compte les données dans ma base de données avec le résultat réel de la requête, j'obtiens des résultats différents.

select f1.user1_id as user1, f2.user1_id as user2, count(f1.user2_id) as 
mutual_count from Friends f1 JOIN Friends f2 ON 
f1.user2_id = f2.user2_id AND f1.user1_id <> f2.user1_id  GROUP BY
f1.user1_id, f2.user1_id order by mutual_count desc

Answer 1

Il y a trois scénarios de jonction que je peux voir.

1 -> 2 -> 3    (mutual friend id between other IDs)    
2 -> 3 -> 1    (mutual friend id > other IDs)    
2 -> 1 -> 3    (mutual friend id < other IDs)    

Ce site peut être résolu avec ce prédicat...

ON f1.user1_id IN (f2.user1_id, f2.user2_id)
OR f1.user2_id IN (f2.user1_id, f2.user2_id)
AND <not joining the row to Itself>

Mais cela perturbera totalement la capacité de l'optimiseur à utiliser les index.

Donc, j'aurais réuni plusieurs requêtes.

(pseudo code car je suis sur un téléphone)

SELECT u1, u2, COUNT(*) FROM
(
    SELECT f1.u1, f2.u2 FROM f1 INNER JOIN f2 ON f1.u2 = f2.u1 AND f1.u1 <> f2.u2
    UNION ALL
    SELECT f1.u1, f2.u1 FROM f1 INNER JOIN f2 ON f1.u2 = f2.u2 AND f1.u1 <> f2.u1
    UNION ALL
    SELECT f1.u2, f2.u2 FROM f1 INNER JOIN f2 ON f1.u1 = f2.u1 AND f1.u2 <> f2.u2
) all_combinations
GROUP BY u1, u2

Chaque requête individuelle sera alors en mesure d'utiliser pleinement les index. (Mettez un index sur u1 et un autre index sur u2 )

Le résultat devrait être un code moins ésotérique (avec des instructions CASE assez longues) et un plan d'exécution beaucoup moins coûteux.