CODEWITHSUNDEEP
mysqlsql
Nasir
Nasir

Reputation: 2122

SQL query to find number of users who are in two specific groups

I have table in MySQL database with two integer columns for example:

userid | groupid
10 | 300
11 | 300
11 | 301
12 | 302

Given two groupids, I am looking for the best and quickest way to find userids which are in both groups. My table contains 23M rows and I need to that for each distinct pair of groupids. Currently both columns are indexed however it takes so long to get the result even for a single pair of groups and I have 1000 distinct groupids. The query I am running now is:

select count(t2.userid) 
from usergroup t1, usergroup t2 
where t1.groupid = 27 and t2.groupid = 714 and t1.userid = t2.userid

Is there a way to do it fast?

Upvotes: 2

Views: 1200

Answers (3)

DRapp
DRapp

Reputation: 48139

I think this might be what you are looking for...

select
     u1.userID
   from
      usergroup u1
         join usergroup u2
            on u2.groupid = 714
           AND u1.userid = u2.userid
   where
      u1.groupid = 27

So, the primary WHERE clause is just give me a list of users within group ID = 27... so this will be optimized exclusively on the GROUP ID. THEN, by doing a self-join to the user groups table matched by the same user ID AND group ID = the 714, it will only return the record if such IS found. I could have a compound index on BOTH Group AND User such as

KEY GroupUser (groupid, userid)

so this way the index will be optimized for BOTH query components...

At MOST, it will go through the first u1 instance one time for everyone in the group 27... No counts or having involved...

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116100

Why the join? 

select 
  u.userid
from
  usergroup u
where
  u.groupid in (27, 714)
group by
  u.userid
having 
  count(u.userid) > 1

Assuming a combination of userid and groupid is unique, which I figure it should be in a table like this.

Upvotes: 2

AjayR
AjayR

Reputation: 4179

It looks correct method for me, but it could be faster by creating prepared statements. See the below post for example.

How can I prevent SQL injection in PHP?

Upvotes: 0

Related Questions