I have query to get all the employees which has no in and out it means they are absent
here is my code:
SELECT emp.employeeid, emp.lastname, emp.firstname, emp.middlename, emp.department
FROM employees AS emp
LEFT OUTER
JOIN attendance_log AS att
ON att.emp_id = emp.employeeid
AND DATE(att.log_time) = '2013-11-05'
WHERE emp.sub LIKE '%REG%'
AND emp.department IN ('Assembly', 'Fabrication', 'Compounding', 'Finishing', 'PET', 'Production', 'Squash', 'QA', 'ENGINEERING')
AND att.emp_id IS NULL;
this code works but the problem is too long to query. it consumes 5-15mins before display the data.
I hope somebody can help me to change my query for faster displaying of data.
but they are of different lengths, sir, so the mysql engine has to extend the shorter one to be the same length as the longer one, i’m guessing, which will be inefficient
your EXPLAIN shows that no indexes are being used whatsoever
so for each row of emp, it has to do a table scan of att – think about that for a moment
okay, next you’re going to ask “but oh sir, what should i do?”
well, think about the two hints i already gave you in this post