I have a problem on this query as I don't know now how to optimize it to make it faster
it takes 20 seconds to load
Employee Attendance View
SELECT
`datetimerecords`.`eid` AS `eid`,
`datetimerecords`.`timedate` AS `timedate`,
`datetimerecords`.`state` AS `state`,
`employees`.`employeeid` AS `employeeid`,
`employees`.`fullname` AS `fullname`,
`employees`.`position` AS `position`,
`departments`.`did` AS `did`,
`departments`.`departmentname` AS `departmentname`,
`schoolyear`.`syid` AS `syid`,
`schoolyear`.`school_year_name` AS `school_year_name`,
`schoolyear`.`school_year_quarter` AS `school_year_quarter`
FROM
(
(
( `datetimerecords` JOIN `employees` ON ( `datetimerecords`.`eid` = `employees`.`employeeid` ) )
JOIN `schoolyear` ON ( `datetimerecords`.`syid` = `schoolyear`.`syid` )
)
JOIN `departments` ON ( `employees`.`did` = `departments`.`did` )
)
ORDER BY
`datetimerecords`.`timedate`
Employees Table
CREATE TABLE `employees` (
`eid_non` int(11) NOT NULL AUTO_INCREMENT,
`did` int(11) DEFAULT NULL,
`esid` int(11) DEFAULT NULL,
`employeeid` varchar(255) DEFAULT NULL COMMENT 'eid',
`fullname` varchar(255) DEFAULT NULL,
`contactnumber` text DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`position` varchar(255) DEFAULT NULL,
PRIMARY KEY (`eid_non`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1290 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=DYNAMIC;
datetimerecords
CREATE TABLE `datetimerecords` (
`aid` int(11) NOT NULL AUTO_INCREMENT,
`eid` int(11) DEFAULT NULL,
`syid` int(11) DEFAULT NULL,
`timedate` varchar(255) DEFAULT NULL,
`state` varchar(255) DEFAULT NULL,
`datecreated` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`aid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=266387 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=DYNAMIC;
departments
CREATE TABLE `departments` (
`did` int(11) NOT NULL AUTO_INCREMENT,
`departmentname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`did`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=DYNAMIC;
Schoolyear Table
CREATE TABLE `schoolyear` (
`syid` int(11) NOT NULL AUTO_INCREMENT,
`school_year_name` varchar(255) DEFAULT NULL,
`school_year_quarter` varchar(255) DEFAULT NULL,
`is_default` enum('yes','no') NOT NULL DEFAULT 'no',
`datecreated` datetime DEFAULT NULL,
PRIMARY KEY (`syid`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci ROW_FORMAT=DYNAMIC;
Have another type of query that can make it faster
Using EXPLAIN to show the query optimization strategy, we see two tables doing table-scans (type: ALL). That's a common performance problem.
+----+-------------+-----------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+----------------------------------------------+
| 1 | SIMPLE | datetimerecords | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | employees | NULL | ALL | employeeid | NULL | NULL | NULL | 1 | 100.00 | Using where; Using join buffer (hash join) |
| 1 | SIMPLE | schoolyear | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.datetimerecords.syid | 1 | 100.00 | NULL |
| 1 | SIMPLE | departments | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.employees.did | 1 | 100.00 | NULL |
+----+-------------+-----------------+------------+--------+---------------+---------+---------+---------------------------+------+----------+----------------------------------------------+
The other joins are eq_ref which is a unique key lookup. That's optimized about as well as we can hope.
Two fixes are needed:
First, add an index on employees(employeeid) so the join can do a lookup efficiently.
ALTER TABLE employees ADD INDEX (employeeid);
But this is not enough, because your join condition is comparing an INT column (datetimerecords.eid) to a VARCHAR column (employees.employeeid).
You can resolve this either by altering the data type in one or the other table, so they match.
You can also resolve it for this query alone by casting the eid column to a string.
SELECT ...
FROM
`datetimerecords`
JOIN `employees` ON CONCAT(`datetimerecords`.`eid`) = `employees`.`employeeid`
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
JOIN `schoolyear` ON `datetimerecords`.`syid` = `schoolyear`.`syid`
JOIN `departments` ON `employees`.`did` = `departments`.`did`
ORDER BY
`datetimerecords`.`timedate`;
With these changes, the EXPLAIN shows that the first join is now doing a lookup on the non-unique index (type: ref).
+----+-------------+-----------------+------------+--------+---------------+------------+---------+---------------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------+------------+--------+---------------+------------+---------+---------------------------+------+----------+------------------------------------+
| 1 | SIMPLE | datetimerecords | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where; Using filesort |
| 1 | SIMPLE | employees | NULL | ref | employeeid | employeeid | 768 | func | 1 | 100.00 | Using index condition; Using where |
| 1 | SIMPLE | schoolyear | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.datetimerecords.syid | 1 | 100.00 | NULL |
| 1 | SIMPLE | departments | NULL | eq_ref | PRIMARY | PRIMARY | 4 | test.employees.did | 1 | 100.00 | NULL |
+----+-------------+-----------------+------------+--------+---------------+------------+---------+---------------------------+------+----------+------------------------------------+
Give that a try and see if it makes the query perform well enough for your needs.