MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE test (idUser INT, idSkill INT);
INSERT INTO test
WITH RECURSIVE
cte AS ( SELECT 1 num
         UNION ALL
         SELECT num + 1 FROM cte WHERE num < 1000 )
SELECT t1.num, 1 + CAST(RAND() * 9 AS UNSIGNED)
FROM cte t1
CROSS JOIN cte t2
WHERE t2.num < 4;
SELECT * FROM test LIMIT 20;


idUser
idSkill


1
2

1
3

1
1

2
5

2
4

2
10

3
9

3
6

3
3

4
4

4
8

4
3

5
9

5
5

5
8

6
4

6
7

6
1

7
4

7
5


SELECT DISTINCT idUser 
FROM test t0
WHERE EXISTS ( SELECT NULL
               FROM test t1
               WHERE t0.idUser = t1.idUser 
                 AND t1.idSkill = 4 )
  AND EXISTS ( SELECT NULL
               FROM test t2
               WHERE t0.idUser = t2.idUser 
                 AND t2.idSkill = 9 );


idUser


26

29

60

103

120

121

132

136

159

174

194

207

260

264

298

309

311

321

325

326

327

332

337

342

349

367

371

374

400

473

491

500

504

513

519

556

574

642

664

703

716

725

735

742

746

782

816

820

826

828

833

848

856

874

888

895

943

969

975

994

996


EXPLAIN
SELECT DISTINCT idUser 
FROM test t0
WHERE EXISTS ( SELECT NULL
               FROM test t1
               WHERE t0.idUser = t1.idUser 
                 AND t1.idSkill = 4 )
  AND EXISTS ( SELECT NULL
               FROM test t2
               WHERE t0.idUser = t2.idUser 
                 AND t2.idSkill = 9 );


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
t0
null
ALL
null
null
null
null
3000
100.00
Using where; Using temporary

1
SIMPLE
<subquery2>
null
eq_ref
<auto_distinct_key>
<auto_distinct_key>
5
db_1445080190.t0.idUser
1
100.00
Distinct

1
SIMPLE
<subquery3>
null
eq_ref
<auto_distinct_key>
<auto_distinct_key>
5
db_1445080190.t0.idUser
1
100.00
Distinct

2
MATERIALIZED
t1
null
ALL
null
null
null
null
3000
10.00
Using where

3
MATERIALIZED
t2
null
ALL
null
null
null
null
3000
10.00
Using where


CREATE INDEX user_skill ON test (idUser, idSkill);
CREATE INDEX skill_user ON test (idSkill, idUser);
EXPLAIN
SELECT DISTINCT idUser 
FROM test t0
WHERE EXISTS ( SELECT NULL
               FROM test t1
               WHERE t0.idUser = t1.idUser 
                 AND t1.idSkill = 4 )
  AND EXISTS ( SELECT NULL
               FROM test t2
               WHERE t0.idUser = t2.idUser 
                 AND t2.idSkill = 9 );


id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra


1
SIMPLE
t2
null
ref
user_skill,skill_user
skill_user
5
const
311
100.00
Using where; Using index; Using temporary; LooseScan

1
SIMPLE
t0
null
ref
user_skill,skill_user
user_skill
5
db_1445080190.t2.idUser
3
100.00
Using index

1
SIMPLE
t1
null
ref
user_skill,skill_user
user_skill
10
db_1445080190.t2.idUser,const
1
100.00
Using index; Distinct; FirstMatch(t0)

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t0	null	ALL	null	null	null	null	3000	100.00	Using where; Using temporary
1	SIMPLE	<subquery2>	null	eq_ref	<auto_distinct_key>	<auto_distinct_key>	5	db_1445080190.t0.idUser	1	100.00	Distinct
1	SIMPLE	<subquery3>	null	eq_ref	<auto_distinct_key>	<auto_distinct_key>	5	db_1445080190.t0.idUser	1	100.00	Distinct
2	MATERIALIZED	t1	null	ALL	null	null	null	null	3000	10.00	Using where
3	MATERIALIZED	t2	null	ALL	null	null	null	null	3000	10.00	Using where

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	t2	null	ref	user_skill,skill_user	skill_user	5	const	311	100.00	Using where; Using index; Using temporary; LooseScan
1	SIMPLE	t0	null	ref	user_skill,skill_user	user_skill	5	db_1445080190.t2.idUser	3	100.00	Using index
1	SIMPLE	t1	null	ref	user_skill,skill_user	user_skill	10	db_1445080190.t2.idUser,const	1	100.00	Using index; Distinct; FirstMatch(t0)