Results 1 to 2 of 2

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

  1. #1
    Join Date
    Oct 2011

    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:
    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.

    Thank you
    Last edited by newphpcoder; 11-07-2013 at 01:47 AM.

  2. #2
    Join Date
    Aug 2004
    emp.sub LIKE '%REG%'
    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?)

    DATE(att.log_time) = '2013-11-05'
    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:
    att.log_time BETWEEN '2013-11-05 00:00:00' AND '2013-11-05 23:59:59.999999'
    "Well done....Consciousness to sarcasm in five seconds!" ~ Terry Pratchett, Night Watch

    How to Ask Questions the Smart Way (not affiliated with this site, but well worth reading)

    My Blog
    cwrBlog: simple, no-database PHP blogging framework

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
HTML5 Development Center