Multiple WHERE subqueries performance

Question

I'm concerned about performance when performing multiple subqueries (8 to be exact) in a SELECT. When tested, it took approximately 5 seconds to find a result.

Given that I have a database with 3 tables: combinations, groups and subjects.

group_1, group_2, group_3 and group_4 reference an id from subjects. subject_id references an id from groups.

Combinations:

| id | group_1 | group_2 | group_3 | group_4 |
|----|---------|---------|---------|---------|
| 1  | 6       | 4       | 6       | 9       |
| 2  | 3       | 16      | 10      | 5       |
| .. | ..      | ..      | ..      | ..      |

Groups:

| id | subject_id | name | max |
|----|------------|------|-----|
| 1  | 2          | 101  | 15  |
| 2  | 2          | 102  | 17  |
| .. | ..         | ..   | ..  |

Subjects:

| id | name      |
|----|-----------|
| 1  | "Science" |
| 2  | "Math"    |
| .. | ..        |

I'm doing the following query to grab an id from combinations given that I know the name of the subjects (i.e. Math, Science, P.E. and Art) and the group (i.e. 101, 102, 104, 101).

SELECT
  id
FROM
  combinations
WHERE
  group_1 = (SELECT id FROM groups WHERE name = $group_name AND subject_id = (SELECT id FROM subjects WHERE name = $subject_name))
AND
  group_2 = (SELECT id FROM groups WHERE name = $group_name AND subject_id = (SELECT id FROM subjects WHERE name = $subject_name))
AND
  group_3 = (SELECT id FROM groups WHERE name = $group_name AND subject_id = (SELECT id FROM subjects WHERE name = $subject_name))
AND
  group_4 = (SELECT id FROM groups WHERE name = $group_name AND subject_id = (SELECT id FROM subjects WHERE name = $subject_name))

Is there any way to optimize the performance? I thought of trying with INNER JOIN, but didn't know how to structure the query.

Answer 1

if I understand your tables and data correctly, your subqueries are testing twice the same thing (name = 101 and name = 'Math'). As a first step, you could replace the subqueries by e.g.

SELECT id 
FROM groups AS g 
INNER JOIN subjects AS s 
ON g.subject_id = s.id 
WHERE name = 'Math' 

However, this will probably not improve the performance. The main issue with the performance is that you still need to join the table combinations 4 times with at least one table (groups). Reorganizing the data in another structure would be a better option.

Answer 2

From what you have provided, here is an alternative I would have written using JOINS, but there is no guarantee per the samples of data you provided.

SELECT
        c.id
    FROM
        combinations c
            JOIN Groups MathGrp
                on c.Group_1 = MathGrp.ID
                AND MathGrp.name = 101
                join Subjects MathSub
                    on MathGrp.subject_id = MathSub.ID
                    AND MathSub.name = 'Math'

            JOIN Groups SciGrp
                on c.Group_2 = SciGrp.ID
                AND SciGrp.name = 102
                join Subjects SciSub
                    on SciGrp.subject_id = SciSub.ID
                    AND SciSub.name = 'Science'

            JOIN Groups PEGrp
                on c.Group_3 = PEGrp.ID
                AND PEGrp.name = 104
                join Subjects PESub
                    on PEGrp.subject_id = PESub.ID
                    AND PESub.name = 'P.E.'

            JOIN Groups ArtGrp
                on c.Group_4 = ArtGrp.ID
                AND ArtGrp.name = 101
                join Subjects ArtSub
                    on ArtGrp.subject_id = ArtSub.ID
                    AND ArtSub.name = 'Art'

However, I think this would be more to your example. It takes whatever is in the combination and joins to the respective groups. From that group, joins to its subject. The return result will show the group name and subject name regardless of Math being in first or last position as similar with the others.

SELECT
        c.id, 
        Grp1.Name as Group1Name,
        Sub1.Name as Subject1Name, 
        Grp2.Name as Group2Name,
        Sub2.Name as Subject2Name, 
        Grp3.Name as Group3Name,
        Sub3.Name as Subject3Name, 
        Grp4.Name as Group4Name,
        Sub4.Name as Subject4Name
    FROM
        combinations c
            JOIN Groups Grp1
                on c.Group_1 = Grp1.ID
                join Subjects Sub1
                    on Grp1.subject_id = Sub1.ID

            JOIN Groups Grp2
                on c.Group_2 = Grp2.ID
                join Subjects Sub2
                    on Grp2.subject_id = Sub2.ID

            JOIN Groups Grp3
                on c.Group_3 = Grp3.ID
                join Subjects Sub3
                    on Grp3.subject_id = Sub3.ID

            JOIN Groups Grp4
                on c.Group_4 = Grp4.ID
                join Subjects Sub4
                    on Grp4.subject_id = Sub4.ID