Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts

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

    Hi,

    I have query to get all the employees which has no in and out it means they are absent


    here is my code:

    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

  • #2
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    Hmm, shouldn't AND DATE(att.log_time) = '2013-11-05' be part of the where clause instead of the join clause?

    Have you tried using EXPLAIN to see what mysql says about your statement?

  • #3
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by FishMonger View Post
    Hmm, shouldn't AND DATE(att.log_time) = '2013-11-05' be part of the where clause instead of the join clause?

    Have you tried using EXPLAIN to see what mysql says about your statement?


    Not yet Sir. How to use EXPLAIN?

    Thank you

  • #4
    Regular Coder
    Join Date
    Aug 2010
    Posts
    713
    Thanks
    165
    Thanked 0 Times in 0 Posts
    Hi Sir,

    Please see attached file for your reference of the output using EXPLAIN in SELECT statement.


    Thank you
    Attached Thumbnails Attached Thumbnails Problem in displaying of data from select statement its consume lots of time-explain.jpg  

  • #5
    Super Moderator
    Join Date
    May 2005
    Location
    Southern tip of Silicon Valley
    Posts
    2,877
    Thanks
    2
    Thanked 164 Times in 159 Posts
    Do you have a primary key configured on those tables?

    Do you have any indexes configured on those tables?

    I suspect that the answer to both of those questions is no.

    Please post the output of these 2 sql statements.

    show create table employees;

    show create table attendance_log;


    Indexes may or may not be needed, but having a primary key in each of those tables should be a must and would be best if they were NOT NULL.

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Code:
    WHERE emp.sub LIKE '%REG%'
    will always result in a table scan because an index can't be used on that column since the search begins with a wild card.


  •  

    Posting Permissions

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