Problem in displaying of data from select statement its consume lots of time
I have query to get all the employees which has no in and out it means they are absent
here is my code:
this code works but the problem is too long to query. it consumes 5-15mins before display the data.
SELECT emp.employeeid, emp.lastname, emp.firstname, emp.middlename, emp.department
FROM employees AS emp
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;
I hope somebody can help me to change my query for faster displaying of data.
Last edited by newphpcoder; 11-07-2013 at 12:47 AM.
As it has to read every eligible record to check that condition, it *might* help to make that the last part of the WHERE clause (though I'm not sure if the DBMS is smart enough to optimize it that way anyway). It would be better, though, if you could optimize the data design so you don't have to use a LIKE, or at least only a LIKE where the wildcard is only at the end of the string, so that it can use the index on that column. (You do have indexes on all columns being used in WHERE and ON clauses, right?)
emp.sub LIKE '%REG%'
This may not be very efficient, either, since it has to do that conversion before the comparison, again likely negating the effect of the index on that column. It might be better to do:
DATE(att.log_time) = '2013-11-05'
att.log_time BETWEEN '2013-11-05 00:00:00' AND '2013-11-05 23:59:59.999999'
"Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
~ Terry Pratchett in Nation
How to Ask Questions the Smart Way
(not affiliated with this site, but well worth reading)
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)