how to add where condition in mysql query before 30 days record

Question

I tried to make a query to get the record 30 days before record. I have two table student and student attend tracking. I tried to get who absent for before 30 days. And my query and result.

SELECT * FROM student st, attend_track at 
WHERE can.can_status = 1 
    AND DATEDIFF(NOW(), DATE(st.created)) > 30
    AND st.std_id = st.std_id 
ORDER BY UNIX_TIMESTAMP(at.created) DESC 

the query return below output.

  ------------------------------------------------------
  | Student name        | Class              | Created |
  ------------------------------------------------------
  | AAA                 | XL                 | 21/02/18|
  ------------------------------------------------------
  | BBB                 | XL                 | 18/02/18|
  ------------------------------------------------------
  | CCC                 | XL                 | 12/02/18|
  ------------------------------------------------------
  | DDD                 | XL                 | 08/02/18|
  ------------------------------------------------------
  | EEE                 | XL                 | 02/02/18|
  ------------------------------------------------------
  | FFF                 | XL                 | 22/01/18|
  ------------------------------------------------------    

But the below record already present that day.

  ------------------------------------------------------
  | Student name        | Class              | Created |
  ------------------------------------------------------
  | CCC                 | XL                 | 10/03/18|
  ------------------------------------------------------
  | DDD                 | XL                 | 20/03/18|
  ------------------------------------------------------
  | FFF                 | XL                 | 28/02/18|
  ------------------------------------------------------    

I tried subquery NOT IN and <> due to large data(more than 200k) is not worked.

SELECT * 
FROM student st, attend_track atr 
WHERE st.std_status = 1 
    AND DATEDIFF(NOW(), DATE(atr.created)) > 30 
    AND st.std_id = atr.std_id  
    AND st.std_id NOT IN (SELECT  atr.std_id
                      FROM attend_track atk 
                      WHERE DATEDIFF(NOW(), DATE(atk.created)) < 30) 
ORDER BY UNIX_TIMESTAMP(atr.created) DESC 

Please suggest me how to optimize the query.

Answer 1

Try this:

SELECT *
FROM (SELECT std_id, MAX(atr.created) last_attended
        FROM attend_track atr
        GROUP BY std_id) lst
INNER JOIN student st ON st.std_id = lst.std_id
WHERE DATEDIFF(NOW(), DATE(last_attended)) > 30 

This is just based on your query. I do not know the table structure or the expected output. To improve the performance of this query, you should take a look at your indexes. Look at the EXPLAIN plan for the query to see what the problem areas are.