Mysql – Update + where not exist

existsMySQLwhere

once again, I come here for your rescue.

how can I improve a query like this for a table with 20 million rows?
I'm trying to update a table, by using the date and getting the first date of attendance for each student

update student_activity c join 
(select * from  student_activity a
where not exists 
        (select * from student_activity b where a.student_id = b.student_id 
                                            and a.class_type = b.class_type
                                            and a.date > b.date)) r
on r.student_id = c.student_id and c.class_type = r.class_type
set c.first_attendance = c.date
;

Best Answer

Your query can be collapsed to the single JOIN without subselects: 

UPDATE      student_activity AS c 
  LEFT JOIN student_activity AS b ON c.student_id = b.student_id 
                                 AND c.class_type = b.class_type
                                 AND c.date > b.date
 WHERE b.date IS NULL 
   SET c.first_attendance = c.date
;

I hope your table student_activity have the index (student_id, class_type, date)

Related Question