CODEWITHSUNDEEP
mysqlsql
Michael Katkov
Michael Katkov

Reputation: 2356

How to make many to many relationship table efficient for SELECT queries in MySQL?

A user can have many interests. An interest can be interested to many users. My database looks like that: 

Users table:
      id - primary key,
      name,
      email,

Interests table:
      id - primary key,
      title

Users_To_Interests table:
      id - primary key,
      user_id(id from users table)
      interest_id(id from interests table)

How can I improve Users_To_Interests table to be able to pick all users who have the same interest efficiently? user_id and interest_id columns don't have indexes or keys. If I need to add them, please show me how can I make that.

Edition 1: For example, 

user1 has interests : interest1, interest2, interest3;
user2 has interests : interest3, interest4;
user3 has interests : interest3, interest5;
user4 has interests : interest4;

If I want to get all users who have interest1, I should receive user1;
If I want to get all users who have interest2, I should receive user1;
If I want to get all users who have interest3, I should receive user1, user2, user3;

Upvotes: 1

Views: 690

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522007

Here is a query which would find all users having interests 1 and 2. It should be clear how to generalize this to any number of interets. The subquery aggregates over users and finds those users who have the interests we want. We then join this back to the Users table to get the full information for each user.

SELECT
    t1.*
FROM Users t1
INNER JOIN
(
    SELECT ui.user_id
    FROM Users_To_Interests ui
    INNER JOIN Interests i
        ON ui.interest_id = i.id
    WHERE i.title IN ('interest2', 'interest3')
    GROUP BY ui.user_id
    HAVING COUNT(DISTINCT i.id) = 2
) t2
    ON t1.id = t2.user_id;

Upvotes: 1

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

The query to get users for interest #3 is very simple (use IN or EXISTS). With an index on users_to_interests(interest_id, user_id) this should be very fast.

select *
from users
where id in (select user_id from users_to_interests where interest_id = 3);

Upvotes: 1

Related Questions