0

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
;
  • It's a very complicated way to get the MIN(date). – Kondybas Jul 10 '18 at 10:24
1

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)

  • This didn't work. I want a situation whereby if there are multiple entries for a student, the first_attendance will display all entries for a student as the min date – edoyak Jul 10 '18 at 14:39
  • I've just simplify your query to avoid redundant operations. If you want the query doing something specific you should formulate what exactly you want. – Kondybas Jul 10 '18 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.