newphpcoder — 2013-11-07T01:45:20-05:00 — #1
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
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.
spacephoenix — 2013-11-07T02:04:39-05:00 — #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?
newphpcoder — 2013-11-07T02:32:32-05:00 — #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
r937 — 2013-11-07T07:58:51-05:00 — #4
part of your problem is that your join columns are of different datatypes
please do an EXPLAIN on the query
newphpcoder — 2013-11-07T19:28:57-05:00 — #5
the EMP_ID and EmployeeID are both varchar Sir.
newphpcoder — 2013-11-07T19:34:24-05:00 — #6
Please see attached file for your reference of the output using EXPLAIN in SELECT statement.
r937 — 2013-11-07T21:30:08-05:00 — #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