MySQL不会永远占用

This query used to take 4 to 5 seconds to run. Here are my table stats:

students Table: 4200 Rows

idtrack Table 15000 Rows

and here is my query:

SELECT students.student_id
FROM students
WHERE students.grade_level ='12' 
AND students.student_id 
NOT IN (
    SELECT idtrack.student_id
    FROM idtrack
    WHERE idtrack.event_id ='33'
)

It is now taking 30 seconds to complete this query. Can someone please help me optimize/refactor? Thanks in advance.

Maybe you forgot create primary kays for tables?

EXISTS is usually a lot faster since, in your case, it allows MySQL to short-circuit when it finds a record in idtrack that contradicts the NOT EXISTS clause.

SELECT students.student_id
FROM students
WHERE students.grade_level ='12' 
AND NOT EXISTS (
SELECT idtrack.student_id
FROM idtrack
WHERE idtrack.event_id ='33'
AND idtrack.student_id = students.student_id
)

What about join students on idtrack, and filter "where students.grade_level=12 and idtrack.event_id <> 33"? Join should be faster than a subquery. Simple equality/inequality much faster than "not in".

You could also try

SELECT students.student_id
FROM students
LEFT JOIN idtrack ON idtrack.event_id='33' AND idtrack.student_id = students.student_id
WHERE students.grade_level ='12' 
AND idtrack.student_id IS NULL

Without seeing the create table commands and explain output its rather difficult. But you might try:

SELECT students.student_id
FROM students LEFT JOIN idtrack
    ON students.id=idtrack.student_id
    AND idtrack.event_id ='33'
WHERE students.grade_level ='12' 
AND idtrack.student_id IS NULL

BTW if idtrack.event_id and students.grade_level are declared as numbers then they shouldn't be quoted.