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

Question

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?

Well the index definitions when I send the create scheme to SQL editor is: KEY `uindex` (`userid`), KEY `gindex` (`groupid`) — Nasir, Jun 16 '11 at 00:50
if you cover your index you will get way better perf ... create a unique index with (groupid,userid) — Sam Saffron, Jun 16 '11 at 01:00

score 2 · Accepted Answer · answered Jun 16 '11 at 00:53

2

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.

answered Jun 16 '11 at 00:53

GolezTrol

114,394
18
182
210

this looks good. only one thing. how can i count the number of rows since using count(u.userid) will output 1 for each userid (because of the group by) – Nasir Jun 16 '11 at 01:15
You don't need to. The `having` makes sure only users are returned that are in more than one (= two) groups. – GolezTrol Jun 16 '11 at 05:45

score 0 · Answer 2 · edited May 23 '17 at 12:04

0

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?

edited May 23 '17 at 12:04

Community

1
1

answered Jun 16 '11 at 00:52

AjayR

4,169
4
44
78

Prepared statements are not faster yet in MySQL. Until recently they even where slower. – GolezTrol Jun 16 '11 at 00:53

score 0 · Answer 3 · answered Jun 16 '11 at 01:22

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...

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

3 Answers3