Slow SQL IN Query

Question

I have the following query. I need information from a table but there are no keys that link these two tables together, and there isn't really a way that it makes sense to reference the other table from either table.

My Query:

SELECT COUNT(*) 
FROM courses as c 
WHERE c.StartYear = 2016 
AND  c.student_id IN (SELECT DISTINCT p.student_id 
                      FROM programs AS p 
                      WHERE p.StartYear = 2016 
                      AND p.program_code NOT LIKE "M%")

This Query takes about 5 seconds to execute, which is way too long. Is there a better way to do this?

Server: MySQL Server version: 5.6.35

Would you be so kind to [DB-Fiddle](https://www.db-fiddle.com/) this? — SirPilan, Jul 04 '18 at 18:22
You say there is no way to link them together, yet they both have a `student_id` and in effect you ***are*** joining the results using that key? Also, if `courses` contains a `student_id`, I take it you're counting student-course combinations, rather than counting courses or counting students? — MatBailie, Jul 04 '18 at 18:24
Do you have the indexes? It doesn't really need to have a link but that doesn't mean they shouldn't have indexes on the fields you search. — Cetin Basoz, Jul 04 '18 at 18:26
@MatBailie The Programs table can have multiple student Id entries and so can the courses table. They don't relate to each other. I've tried INNER and LEFT joining them but that takes 10 seconds or longer. — Simon Chawla, Jul 04 '18 at 18:28
@SimonChawla - Define "they don't relate to each other" because in your example query you've intimated they ***do*** relate to each other... You specifically say `WHERE c.student_id IN ( )` That's almost exactly a join on `student_id`... Perhaps you would benefit from showing us a dumy set of dat with the expected results that you're looking for? — MatBailie, Jul 04 '18 at 18:30
Please post the table schema and the indexes that you have on the columns. Those will really help us help you. — David Buttrick, Jul 04 '18 at 18:31

score 0 · Answer 1 · answered Jul 04 '18 at 18:20

Try exists

SELECT COUNT(*) 
  FROM courses as c 
 WHERE c.StartYear = 2016 
   and exists (SELECT 1
                 FROM programs AS p 
                WHERE p.StartYear = 2016 
                  AND p.program_code NOT LIKE "M%" 
                  and c.student_id = p.student_id)

score 0 · Answer 2 · answered Jul 04 '18 at 18:25

0

The exists should work better than In clause, so you can try this query. Click here for related discussion.

SELECT COUNT(*) 
FROM courses as c 
WHERE c.StartYear = 2016 
AND Exists (SELECT 1
            FROM programs AS p 
            WHERE p.StartYear = 2016 
            AND student_id = c.student_id
            AND p.program_code NOT LIKE "M%")

answered Jul 04 '18 at 18:25

Vinit

2,540
1
15
22

Thank for the related discussion. I read through it and I have tried this, but it really does take twice as long – Simon Chawla Jul 04 '18 at 18:31

score 0 · Accepted Answer · answered Jul 04 '18 at 18:34

This should give the exact same results, and may or may not be faster, but does demonstrate that at present your query does intimate that the two tables can be related by student_id.

If the query below is logically incorrect, so is your example query, and you should give a dummy example to demonstrate the logic you want : https://stackoverflow.com/help/mcve

SELECT
  COUNT(*)
FROM
  courses    c
INNER JOIN
(
  SELECT
    student_id,
    startYear
  FROM
    programs
  WHERE
    program_code NOT LIKE "M%"
  GROUP BY
    student_id,
    startYear
)
  p
    ON  p.student_id = c.student_id
    AND p.startYear  = c.startYear
WHERE
  c.StartYear = 2016

Slow SQL IN Query

3 Answers3