Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Query with JOIN is so slow

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

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    Indexes:

    datetimerecords:  INDEX(timedate,  state, syid, eid)
employees:  INDEX(employeeid,  fullname, position, did)

    Why isn’t employeeid the PRIMARY KEY of employees? Might it not be unique? Might it be NULL? Does it really need to be VARCHAR(255)?

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search