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

Databases
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;

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

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?

3 

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



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

part of your problem is that your join columns are of different datatypes

please do an EXPLAIN on the query

5

How Sir?

the EMP_ID and EmployeeID are both varchar Sir.

Thank you

6

Hi Sir,

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

Thank you

7

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