I need to optimize this MYSQL query

SELECT COUNT(student_id) AS count 
FROM student_details 
WHERE STATUS='REGISTER'
    AND student_id NOT IN (
        SELECT student_id FROM student_details  WHERE STATUS='CANCEL'
    )
    AND registered_on< '2020-10-15 00:00:00'

I have tried NOT EXIST but didn’t get expected result

SELECT COUNT(DISTINCT  S.student_id) AS ren 
FROM student_details S
WHERE 
    S.status = 'REGISTER'
    AND S.registered_on < '2020-10-15 00:00:00'
    AND NOT EXISTS ( 
        SELECT 1 
        FROM  student_details S1 
        WHERE S.student_id = S1.student_id AND S1.status = 'CANCEL'
    )

can’t do index since duplicate entries of student_id,status are valid entries,Need to reduce execution time since the table has large number of data.

Answer

You can try query with JOIN condition:

SELECT COUNT(student_details.student_id) AS count 
FROM student_details 
LEFT JOIN subscription 
    ON subscription.student_id = student_details.student_id AND subscription.status = 'CANCEL'
WHERE 
    student_details.status='REGISTER'
    AND subscription.status IS NULL
    AND registered_on< '2020-10-15 00:00:00';

Here the fiddle SQLize.online

Sure that your tables have index on student_id field. Since you filter by status field, so index on this field can improve the query performance