基于interestID检索指定userID的Top20共同兴趣用户的MySQL问询

Hey there! Let's tackle this problem head-on—it's a classic use case for finding similar users, and we can get it working efficiently with the right SQL and indexing tweaks.

Core Query Approach #

The key idea is to first fetch all interests for your target user, then match those interests against other users and count how many overlaps each has. Here's a straightforward query that does exactly that:

SELECT 
    ui2.userID AS similar_user_id,
    COUNT(ui2.interestID) AS common_interests_count
FROM 
    user_interests ui1
JOIN 
    user_interests ui2 ON ui1.interestID = ui2.interestID
WHERE 
    ui1.userID = 123  -- Replace with your target userID
    AND ui2.userID != 123  -- Exclude the target user themselves
GROUP BY 
    ui2.userID
ORDER BY 
    common_interests_count DESC, similar_user_id ASC
LIMIT 20;

Let me break this down:

• We use ui1 to grab all interests belonging to the target user.
• We join with ui2 (the same table) to find every other user that shares any of those interests.
• Grouping by ui2.userID lets us count how many shared interests each user has.
• Sorting by common_interests_count DESC ensures we get the most similar users first; adding similar_user_id ASC gives consistent results when two users have the same number of shared interests.
• Finally, LIMIT 20 narrows it down to just the top 20 matches.

Optimize for Speed with Indexes #

If your user_interests table has thousands or more rows, this query might run slow without indexes. Add these two composite indexes to drastically improve performance:

-- Index to quickly fetch all interests for a specific user
CREATE INDEX idx_user_interest ON user_interests(userID, interestID);

-- Index to quickly find all users associated with a specific interest
CREATE INDEX idx_interest_user ON user_interests(interestID, userID);

The first index makes fetching the target user's interests a lightning-fast lookup. The second index speeds up the join operation, so MySQL doesn't have to scan the entire table to find users matching each interest.

Advanced: Precompute Similarities for Ultra-Fast Queries #

If you're dealing with a massive dataset (millions of rows) and need sub-second response times, precomputing user similarities is a great approach. Here's how to set it up:

1. Create a table to store precomputed similarities:

   CREATE TABLE user_similarity (
       userID INT NOT NULL,
       similar_userID INT NOT NULL,
       common_interests INT NOT NULL,
       PRIMARY KEY (userID, similar_userID),
       INDEX idx_similar_user (similar_userID, common_interests)
   );
   

2. Run a periodic job (using MySQL Events, cron, or your backend framework's task scheduler) to update this table. Here's a sample query to populate it:

   REPLACE INTO user_similarity (userID, similar_userID, common_interests)
   SELECT 
       ui1.userID,
       ui2.userID,
       COUNT(ui2.interestID) AS common_interests
   FROM 
       user_interests ui1
   JOIN 
       user_interests ui2 ON ui1.interestID = ui2.interestID
   WHERE 
       ui1.userID != ui2.userID
   GROUP BY 
       ui1.userID, ui2.userID;
   

Then, when you need the top 20 similar users for a target user, just query this precomputed table:

SELECT similar_userID, common_interests
FROM user_similarity
WHERE userID = 123
ORDER BY common_interests DESC, similar_userID ASC
LIMIT 20;

This approach trades off a bit of real-time accuracy for blazingly fast queries—perfect if you don't need instant updates when a user adds a new interest.

内容的提问来源于stack exchange，提问作者Jake Cross