SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,016
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Can you please post the output of a SHOW CREATE TABLE for both tables and some sample data for each table?

    How many records are in each table?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SpacePhoenix View Post
    Can you please post the output of a SHOW CREATE TABLE for both tables and some sample data for each table?

    How many records are in each table?
    Code:
    CREATE TABLE `employees` (
     `EmployeeID` varchar(255) DEFAULT NULL,
     `EmployeeCode` varchar(50) DEFAULT NULL,
     `Lastname` varchar(255) DEFAULT NULL,
     `Firstname` varchar(255) DEFAULT NULL,
     `Middlename` varchar(255) DEFAULT NULL,
     `Birthday` datetime DEFAULT NULL,
     `CurrentAddress` varchar(255) DEFAULT NULL,
     `ProvincialAddress` varchar(255) DEFAULT NULL,
     `CivilStatus` varchar(255) DEFAULT NULL,
     `Sex` varchar(255) DEFAULT NULL,
     `Sub` varchar(50) DEFAULT NULL,
     `Department` varchar(255) DEFAULT NULL,
     `ScheduleCode` varchar(255) DEFAULT NULL,
     `StartDate` datetime DEFAULT NULL,
     `EndDate` datetime DEFAULT NULL,
     `PicturePath` varchar(255) DEFAULT NULL,
     `deleted` int(11) DEFAULT '1'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    Code:
    CREATE TABLE `attendance_log` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `EMP_ID` varchar(20) DEFAULT NULL,
     `LOG_TIME` datetime DEFAULT NULL,
     `INDICATOR` varchar(5) DEFAULT NULL,
     PRIMARY KEY (`id`),
     KEY `EMP_ID` (`EMP_ID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=602651 DEFAULT CHARSET=latin1
    employees table = 1020 data
    attendance_log = 602647

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    part of your problem is that your join columns are of different datatypes

    please do an EXPLAIN on the query
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    part of your problem is that your join columns are of different datatypes

    please do an EXPLAIN on the query
    How Sir?

    the EMP_ID and EmployeeID are both varchar Sir.


    Thank you

  6. #6
    SitePoint Guru
    Join Date
    Oct 2011
    Posts
    658
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Sir,

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


    Thank you
    Attached Images Attached Images

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newphpcoder View Post
    the EMP_ID and EmployeeID are both varchar Sir.
    but they are of different lengths, sir, so the mysql engine has to extend the shorter one to be the same length as the longer one, i'm guessing, which will be inefficient

    your EXPLAIN shows that no indexes are being used whatsoever

    so for each row of emp, it has to do a table scan of att -- think about that for a moment

    okay, next you're going to ask "but oh sir, what should i do?"

    well, think about the two hints i already gave you in this post
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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