1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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,500
    Likes Received:
    4,460
    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