Group By a specific value on a column for faster execution time - SQL

This code works perfectly but the execution time takes long, because the attendance_03 has the same count of rows as working_schedule_03 which really need each rows because working_schedule_03 have a shift schedule like 8:00, 9:00 and so on and it is where attendance_03 base its late employees, attendance_03 have the actual time in of an employee, now what I want is to group some values in multiple columns in working_schedule_03 table which is emp_id column and t_in(shift schedule) column because the t_in is not fixed, employees changes schedule every week and others have fixed sched and lastly eve column which have "Regular" value and to ungroup the "Rest day" values so attendance_03 will not get the rows from eve column which have rest day values. thanks!

 SELECT 
    a.emp_id, COUNT(a.a_id) AS Cymon, b.t_in AS mt_in, c.last_name, c.first_name, e.dept_name, e.dept_id, f.l_id, f.loc
 FROM
     attendance_03 AS a
    LEFT JOIN 
      (SELECT * FROM working_schedule_03 WHERE d BETWEEN 16 AND 31 AND Y = 2015 AND eve="Regular") AS b ON b.emp_id = a.emp_id AND b.d = a.dd
    LEFT JOIN 
      employee_personal_info AS c ON c.emp_id = a.emp_id 
    LEFT JOIN 
      employee_information AS d ON d.emp_id = a.emp_id 
    LEFT JOIN 
      departments AS e ON e.dept_id = d.department 
    LEFT JOIN 
      employee_location AS f ON f.l_id = d.loc 
  WHERE a.yy = 2015 AND a.dd BETWEEN 16 AND 31 AND b.eve = "Regular" AND e.dept_id != 22
      AND
  CASE 
    WHEN b.t_in = "07:00" THEN HOUR(a.t_in) = "07" AND MINUTE(a.t_in) BETWEEN 01 AND 59 
    WHEN b.t_in = "07:30" THEN HOUR(a.t_in) = "07" AND MINUTE(a.t_in) BETWEEN 31 AND 59 OR HOUR(a.t_in) = "08" AND MINUTE(a.t_in) BETWEEN 01 AND 30
    WHEN b.t_in = "08:00" THEN HOUR(a.t_in) = "08" AND MINUTE(a.t_in) BETWEEN 01 AND 59 
    WHEN b.t_in = "08:30" THEN HOUR(a.t_in) = "08" AND MINUTE(a.t_in) BETWEEN 31 AND 59 OR HOUR(a.t_in) = "09" AND MINUTE(a.t_in) BETWEEN 01 AND 30
    WHEN b.t_in = "09:00" THEN HOUR(a.t_in) = "09" AND MINUTE(a.t_in) BETWEEN 01 AND 59 
    WHEN b.t_in = "10:00" THEN HOUR(a.t_in) = "10" AND MINUTE(a.t_in) BETWEEN 01 AND 59 
    WHEN b.t_in = "13:00" THEN HOUR(a.t_in) = "13" AND MINUTE(a.t_in) BETWEEN 01 AND 59 
    WHEN b.t_in = "15:00" THEN HOUR(a.t_in) = "15" AND MINUTE(a.t_in) BETWEEN 01 AND 59 
    WHEN b.t_in = "16:00" THEN HOUR(a.t_in) = "16" AND MINUTE(a.t_in) BETWEEN 01 AND 59 
    WHEN b.t_in = "17:00" THEN HOUR(a.t_in) = "17" AND MINUTE(a.t_in) BETWEEN 01 AND 59 
    WHEN b.t_in = "19:00" THEN HOUR(a.t_in) = "19" AND MINUTE(a.t_in) BETWEEN 01 AND 59 
    WHEN b.t_in = "19:30" THEN HOUR(a.t_in) = "19" AND MINUTE(a.t_in) BETWEEN 31 AND 59 OR HOUR(a.t_in) = "20" AND MINUTE(a.t_in) BETWEEN 01 AND 30
    WHEN b.t_in = "20:00" THEN HOUR(a.t_in) = "20" AND MINUTE(a.t_in) BETWEEN 01 AND 59 
    WHEN b.t_in = "21:00" THEN HOUR(a.t_in) = "21" AND MINUTE(a.t_in) BETWEEN 01 AND 59 
    WHEN b.t_in = "23:45" THEN HOUR(a.t_in) = "23" AND MINUTE(a.t_in) BETWEEN 46 AND 59 OR HOUR(a.t_in) = "20" AND MINUTE(a.t_in) BETWEEN 01 AND 45
  ELSE 0 END 
  GROUP BY a.emp_id
  HAVING COUNT(a.emp_id) > 1
  ORDER BY f.loc, e.dept_id, c.last_name ASC

working_schedule_03 table

CREATE TABLE `working_schedule_03` (
  `id` mediumint(99) NOT NULL AUTO_INCREMENT,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `username` varchar(255) NOT NULL,
  `m` varchar(9) NOT NULL,
  `d` varchar(9) NOT NULL,
  `y` varchar(9) NOT NULL,
  `emp_id` varchar(99) NOT NULL,
  `eve` varchar(99) NOT NULL,
  `t_in` varchar(99) NOT NULL,
  `t_out` varchar(99) NOT NULL,
  `emp_file` varchar(99) NOT NULL,
  `dt_plot` varchar(99) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=612993 DEFAULT CHARSET=latin1

attendance_03 table

CREATE TABLE `attendance_03` (
  `a_id` varchar(99) NOT NULL,
  `t_in` varchar(99) NOT NULL,
  `t_b1_out` varchar(99) NOT NULL,
  `t_b1_in` varchar(99) NOT NULL,
  `t_lb_out` varchar(99) NOT NULL,
  `t_lb_in` varchar(99) NOT NULL,
  `t_b2_out` varchar(99) NOT NULL,
  `t_b2_in` varchar(99) NOT NULL,
  `t_out` varchar(99) NOT NULL,
  `mu` varchar(99) NOT NULL,
  `status` varchar(99) NOT NULL,
  `mm` varchar(99) NOT NULL,
  `dd` varchar(99) NOT NULL,
  `yy` varchar(99) NOT NULL,
  `d_out` int(2) NOT NULL,
  `d_b1_out` int(2) NOT NULL,
  `d_b1_in` int(2) NOT NULL,
  `d_lb_out` int(2) NOT NULL,
  `d_lb_in` int(2) NOT NULL,
  `d_b2_out` int(2) NOT NULL,
  `d_b2_in` int(2) NOT NULL,
  `emp_id` varchar(99) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Solution

Adding INDEXes will speed it up significantly:

working_schedule_03 would benefit from INDEX(Y, d, eve).
attendance_03 would benefit from INDEX(emp_id, dd).
employee_personal_info and employee_information: INDEX(emp_id).
departments: INDEX(e.dept_id)
employee_location: INDEX(f.l_id)

Get rid of LEFT unless you expect the 'right' table to be missing matching rows.

The second occurrence of AND b.eve = "Regular" is redundant (because of the first).

The subquery says SELECT *. It would be less stuff to haul around if you list only the necessary values (emp_id, t_in, d).

I gag on other things:

Don't use VARCHAR for numbers.
Consider using TIME for times. (But there may be an issue with the :00 seconds.)
The big CASE could almost be turned into

a.t_in >= b.t_in AND a.in < b.t_in + INTERVAL 60 MINUTE
Check the output; the COUNT(...) may have a bigger value than needed.