Problem in displaying of data from select statement its consume lots of time

Discussion in 'Databases' started by newphpcoder, Nov 6, 2013.

0
  1. #1
    Hi,
    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;
    Code (markup):
    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.


    Thank you
     
    newphpcoder, Nov 6, 2013 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,808
    Likes Received:
    4,534
    Best Answers:
    123
    Trophy Points:
    665
    #2
    You might have to play around with this for a bit to get it right - I'm assuming your query is slow from phpMyAdmin as well as from the page.

    how about
    SELECT emp.employeeid, emp.lastname, emp.firstname, emp.middlename, emp.department
  FROM employees AS emp
   WHERE emp.sub LIKE '%REG%'
  AND emp.department IN ('Assembly', 'Fabrication', 'Compounding', 'Finishing', 'PET', 'Production', 'Squash', 'QA', 'ENGINEERING')
  AND emp.employeeid not in (select emp_id from attendance_log as att where DATE(att.log_time) = '2013-11-05')
    Code (markup):
    You also need to look at the indexes - get an "explain" on each version of the query
    putting a like on emp.sub will slow it down, may not be the issue but is there another way around that clause?
     
    sarahk, Nov 13, 2013 IP