Mysql comparison between two datasets

Question

I'm having troubles with mysql, I guess I'm missing some special functions to solve my problem. I have a table, like this:

id - user_id - user_property

1  -  45  -  9986
2  -  45  -  9564
3  -  45  -  9225
4  -  45  -  9824
5  -  45  -  9711
6  -  83  -  9711
7  -  83  -  9924
8  -  83  -  9986
9  -  12  -  9933
10 -  12  -  9993
11 -  72  -  9189
12 -  72  -  9711
13 -  72  -  9225
14 -  72  -  9824

user_id+user_property is unique key

and I have a list of properties, like "9711","9225","9824". I'm trying to get a list of users having ALL those properties, in the most performant way possible. I've tried many ways, like doing 3 single queries and counting results like this

select count(distinct user_id) as tot from 
(     select user_id from mytable where user_property = 9711
union select user_id from mytable where user_property = 9225
union select user_id from mytable where user_property = 9824) as tmp
having tot = 3

another guess was to merge user properties per-user and searching wanted properties with the function FIND_IN_SET(element, set of elements obtained with GROUP_CONCAT(user_properties separator ','))

the problem is, mytable is really huge, I already need to select data from this and another table joined by user_id (and elaborate results another time after) and I'm guessing there is some better way to do that in terms of performances. any suggestions?

Thanks in advance

Answer 1

What about something like this?

SELECT COUNT(user_id) FROM (
    SELECT user_id
    FROM mytable
    WHERE user_property IN (9711,9225,9824)
    GROUP BY user_id
    HAVING COUNT(*) >= 3
) users_with_all_properties

Answer 2

You can combine SUM with IN CLAUSE

SELECT COUNT(*)
FROM
(SELECT 
user_id,SUM(user_property in (9711, 9225,9824)) sumprop
FROM mytable 
GROUP BY user_id
Having sumprop = 3) userhasprop

| COUNT(*) |
| -------: |
|        2 |

db<>fiddle here