14

Below query is taking more than 11 minutes to execute. 

SELECT `c`.*, 
       `e`.`name`                                                 AS `employee_name`, 
       `e`.`emp_no`, 
       `d`.`code`                                                 AS `department_code`, 
       IF(ew.code IS NOT NULL, ew.code, egw.code)                 AS shift_code, 
       IF(ew.code IS NOT NULL, ew.time_in_from, egw.time_in_from) AS time_in_from, 
       IF(ew.code IS NOT NULL, ew.time_out_to, egw.time_out_to)   AS time_out_to, 
       IF(ew.code IS NOT NULL, ew.next_day, egw.next_day)         AS next_day 
FROM   `tms_emp_badge_card` AS `c` 
       LEFT JOIN `tms_door_record_raw` AS `dr` 
              ON `c`.`card_no` = `dr`.`card_no` 
       LEFT JOIN `tms_employee` AS `e` 
              ON `c`.`emp_no` = `e`.`emp_no` 
       LEFT JOIN `tms_emp_group` AS `g` 
              ON `e`.`group_id` = `g`.`id` 
       LEFT JOIN `tms_emp_department` AS `d` 
              ON `e`.`department_id` = `d`.`id` 
       LEFT JOIN `tms_emp_workschedule` AS `ewt` 
              ON `ewt`.`workschedule_date` = "2016-11-01" 
                 AND ( ewt.reference_no = c.emp_no 
                       AND ewt.reference_type = "emp_no" ) 
       LEFT JOIN `tms_workschedule` AS `ew` 
              ON `ewt`.`workschedule_id` = `ew`.`id` 
       LEFT JOIN `tms_emp_workschedule` AS `egwt` 
              ON `egwt`.`workschedule_date` = "2016-11-01" 
                 AND ( egwt.reference_no = g.code 
                       AND egwt.reference_type = "group_code" ) 
       LEFT JOIN `tms_workschedule` AS `egw` 
              ON `egwt`.`workschedule_id` = `egw`.`id` 
WHERE  `dr`.`record_time` BETWEEN '2016-11-01' AND '2016-11-02' 
GROUP  BY `c`.`card_no` 
ORDER  BY c.emp_no

Below is the explain query

+----+-------------+-------+------------+--------+-------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type   | possible_keys                                                           | key                                     | key_len | ref                      | rows | filtered | Extra                           |
+----+-------------+-------+------------+--------+-------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL    | tms_emp_badge_card_card_no_index,emp_card_no                            | NULL                                    | NULL    | NULL                     |  884 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | e     | NULL       | eq_ref | tms_employee_emp_no_unique                                              | tms_employee_emp_no_unique              | 767     | tms.c.emp_no             |    1 |   100.00 | NULL                            |
|  1 | SIMPLE      | g     | NULL       | eq_ref | PRIMARY                                                                 | PRIMARY                                 | 4       | tms.e.group_id           |    1 |   100.00 | NULL                            |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY                                                                 | PRIMARY                                 | 4       | tms.e.department_id      |    1 |   100.00 | NULL                            |
|  1 | SIMPLE      | dr    | NULL       | ref    | tms_door_record_raw_card_no_index,tms_door_record_raw_record_time_index | tms_door_record_raw_card_no_index       | 767     | tms.c.card_no            |  276 |     1.27 | Using where                     |
|  1 | SIMPLE      | ewt   | NULL       | ref    | tms_emp_workschedule_reference_no_index,workschedule_date               | tms_emp_workschedule_reference_no_index | 767     | tms.c.emp_no             |   83 |   100.00 | Using where                     |
|  1 | SIMPLE      | ew    | NULL       | eq_ref | PRIMARY                                                                 | PRIMARY                                 | 4       | tms.ewt.workschedule_id  |    1 |   100.00 | NULL                            |
|  1 | SIMPLE      | egwt  | NULL       | ref    | tms_emp_workschedule_reference_no_index,workschedule_date               | tms_emp_workschedule_reference_no_index | 767     | tms.g.code               |   83 |   100.00 | Using where                     |
|  1 | SIMPLE      | egw   | NULL       | eq_ref | PRIMARY                                                                 | PRIMARY                                 | 4       | tms.egwt.workschedule_id |    1 |   100.00 | NULL                            |
+----+-------------+-------+------------+--------+-------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+---------------------------------+

Table structure

CREATE TABLE `tms_emp_badge_card` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `import_id` int(11) DEFAULT NULL,
  `emp_no` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `card_no` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tms_emp_badge_card_import_id_unique` (`import_id`),
  KEY `tms_emp_badge_card_emp_no_index` (`emp_no`),
  KEY `tms_emp_badge_card_card_no_index` (`card_no`),
  KEY `emp_card_no` (`card_no`,`emp_no`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=885 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


CREATE TABLE `tms_door_record_raw` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `import_id` int(11) DEFAULT NULL,
  `card_no` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `door_no` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `controller_no` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `record_time` datetime NOT NULL,
  `record_state` int(11) NOT NULL,
  `open_type` int(11) NOT NULL,
  `pass_flag` int(11) NOT NULL,
  `hand_value` int(11) NOT NULL,
  `lfeet_value` int(11) NOT NULL,
  `rfeet_value` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tms_door_record_raw_import_id_unique` (`import_id`),
  KEY `tms_door_record_raw_card_no_index` (`card_no`),
  KEY `tms_door_record_raw_door_no_index` (`door_no`),
  KEY `tms_door_record_raw_controller_no_index` (`controller_no`),
  KEY `tms_door_record_raw_record_time_index` (`record_time`)
) ENGINE=InnoDB AUTO_INCREMENT=368713 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci


 CREATE TABLE `tms_employee` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `import_id` int(11) DEFAULT NULL,
  `emp_no` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `plant_id` int(10) unsigned DEFAULT NULL,
  `department_id` int(10) unsigned DEFAULT NULL,
  `group_id` int(10) unsigned DEFAULT NULL,
  `attendance_group_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tms_employee_emp_no_unique` (`emp_no`),
  UNIQUE KEY `tms_employee_import_id_unique` (`import_id`),
  KEY `tms_employee_plant_id_foreign` (`plant_id`),
  KEY `tms_employee_department_id_foreign` (`department_id`),
  KEY `tms_employee_group_id_foreign` (`group_id`),
  CONSTRAINT `tms_employee_department_id_foreign` FOREIGN KEY (`department_id`) REFERENCES `tms_emp_department` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tms_employee_group_id_foreign` FOREIGN KEY (`group_id`) REFERENCES `tms_emp_group` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tms_employee_plant_id_foreign` FOREIGN KEY (`plant_id`) REFERENCES `tms_emp_plant` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=865 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

p_no`, 
       `d (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `import_id` int(11) DEFAULT NULL,
  `code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tms_emp_group_import_id_unique` (`import_id`),
  KEY `tms_emp_group_code_index` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `tms_emp_department` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `import_id` int(11) DEFAULT NULL,
  `code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tms_emp_department_import_id_unique` (`import_id`),
  KEY `tms_emp_department_code_index` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=82 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `tms_emp_workschedule` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `import_id` int(11) DEFAULT NULL,
  `reference_type` enum('emp_no','plant_code','department_code','group_code') COLLATE utf8_unicode_ci NOT NULL,
  `reference_no` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `workschedule_id` int(10) unsigned NOT NULL,
  `workschedule_date` date NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tms_emp_workschedule_import_id_unique` (`import_id`),
  KEY `tms_emp_workschedule_reference_no_index` (`reference_no`),
  KEY `tms_emp_workschedule_workschedule_id_foreign` (`workschedule_id`),
  KEY `workschedule_date` (`workschedule_date`),
  CONSTRAINT `tms_emp_workschedule_workschedule_id_foreign` FOREIGN KEY (`workschedule_id`) REFERENCES `tms_workschedule` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=27597 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

CREATE TABLE `tms_workschedule` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `import_id` int(11) DEFAULT NULL,
  `code` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `description` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `time_in` time NOT NULL,
  `time_in_from` time NOT NULL,
  `time_in_to` time NOT NULL,
  `time_out` time NOT NULL,
  `time_out_from` time NOT NULL,
  `time_out_to` time NOT NULL,
  `next_day` tinyint(1) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tms_workschedule_import_id_unique` (`import_id`),
  KEY `tms_workschedule_code_index` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=45 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

I am thinking whether is the "Using temporary; Using filesort problem", or I should create multi column index but I don't know how to fix it. Please advise.

Update 1:

After adding multi column index on tms_door_record_raw table (KEY card_no_record_time (card_no,record_time)) The sql execution went down from 11mins to 3.2 seconds

Execute the explain sql again. The join table key for dr has change from (card_no) to (card_no, record_time) also on the extra column it is showing Using where; Using index

+----+-------------+-------+------------+--------+---------------------------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+---------------------------------+
| id | select_type | table | partitions | type   | possible_keys                                                                               | key                                     | key_len | ref                      | rows | filtered | Extra                           |
+----+-------------+-------+------------+--------+---------------------------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+---------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL    | tms_emp_badge_card_card_no_index,emp_card_no                                                | NULL                                    | NULL    | NULL                     |  884 |   100.00 | Using temporary; Using filesort |
|  1 | SIMPLE      | e     | NULL       | eq_ref | tms_employee_emp_no_unique                                                                  | tms_employee_emp_no_unique              | 767     | tms.c.emp_no             |    1 |   100.00 | NULL                            |
|  1 | SIMPLE      | g     | NULL       | eq_ref | PRIMARY                                                                                     | PRIMARY                                 | 4       | tms.e.group_id           |    1 |   100.00 | NULL                            |
|  1 | SIMPLE      | d     | NULL       | eq_ref | PRIMARY                                                                                     | PRIMARY                                 | 4       | tms.e.department_id      |    1 |   100.00 | NULL                            |
| *1 | SIMPLE      | dr    | NULL       | ref    | tms_door_record_raw_card_no_index,tms_door_record_raw_record_time_index,record_time_card_no | record_time_card_no                     | 767     | tms.c.card_no            |  266 |     1.27 | Using where; Using index        |
|  1 | SIMPLE      | ewt   | NULL       | ref    | tms_emp_workschedule_reference_no_index,workschedule_date                                   | tms_emp_workschedule_reference_no_index | 767     | tms.c.emp_no             |   83 |   100.00 | Using where                     |
|  1 | SIMPLE      | ew    | NULL       | eq_ref | PRIMARY                                                                                     | PRIMARY                                 | 4       | tms.ewt.workschedule_id  |    1 |   100.00 | NULL                            |
|  1 | SIMPLE      | egwt  | NULL       | ref    | tms_emp_workschedule_reference_no_index,workschedule_date                                   | tms_emp_workschedule_reference_no_index | 767     | tms.g.code               |   83 |   100.00 | Using where                     |
|  1 | SIMPLE      | egw   | NULL       | eq_ref | PRIMARY                                                                                     | PRIMARY                                 | 4       | tms.egwt.workschedule_id |    1 |   100.00 | NULL                            |
+----+-------------+-------+------------+--------+---------------------------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+---------------------------------+

Update 2: Remove the dr join sql and replace by where exist condition suggested by @mendosi change the execution time to 0.60 seconds.

SELECT `c`.*, 
       `e`.`name`                                                 AS `employee_name`, 
       `e`.`emp_no`, 
       `d`.`code`                                                 AS `department_code`, 
       IF(ew.code IS NOT NULL, ew.code, egw.code)                 AS shift_code, 
       IF(ew.code IS NOT NULL, ew.time_in_from, egw.time_in_from) AS time_in_from, 
       IF(ew.code IS NOT NULL, ew.time_out_to, egw.time_out_to)   AS time_out_to, 
       IF(ew.code IS NOT NULL, ew.next_day, egw.next_day)         AS next_day 
FROM   `tms_emp_badge_card` AS `c`  
       LEFT JOIN `tms_employee` AS `e` 
              ON `c`.`emp_no` = `e`.`emp_no` 
       LEFT JOIN `tms_emp_group` AS `g` 
              ON `e`.`group_id` = `g`.`id` 
       LEFT JOIN `tms_emp_department` AS `d` 
              ON `e`.`department_id` = `d`.`id` 
       LEFT JOIN `tms_emp_workschedule` AS `ewt` 
              ON `ewt`.`workschedule_date` = "2016-11-01" 
                 AND ( ewt.reference_no = c.emp_no 
                       AND ewt.reference_type = "emp_no" ) 
       LEFT JOIN `tms_workschedule` AS `ew` 
              ON `ewt`.`workschedule_id` = `ew`.`id` 
       LEFT JOIN `tms_emp_workschedule` AS `egwt` 
              ON `egwt`.`workschedule_date` = "2016-11-01" 
                 AND ( egwt.reference_no = g.code 
                       AND egwt.reference_type = "group_code" ) 
       LEFT JOIN `tms_workschedule` AS `egw` 
              ON `egwt`.`workschedule_id` = `egw`.`id` 
WHERE EXISTS (SELECT 1 FROM tms_door_record_raw As dr WHERE c.card_no = dr.card_no AND dr.record_time BETWEEN '2016-11-01' AND '2016-11-02') 
ORDER BY c.emp_no;

Below is the explain sql

+----+--------------------+-------+------------+--------+---------------------------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+--------------------------+
| id | select_type        | table | partitions | type   | possible_keys                                                                               | key                                     | key_len | ref                      | rows | filtered | Extra                    |
+----+--------------------+-------+------------+--------+---------------------------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+--------------------------+
|  1 | PRIMARY            | c     | NULL       | ALL    | NULL                                                                                        | NULL                                    | NULL    | NULL                     |  884 |   100.00 | Using where              |
|  1 | PRIMARY            | e     | NULL       | eq_ref | tms_employee_emp_no_unique                                                                  | tms_employee_emp_no_unique              | 767     | tms.c.emp_no             |    1 |   100.00 | NULL                     |
|  1 | PRIMARY            | g     | NULL       | eq_ref | PRIMARY                                                                                     | PRIMARY                                 | 4       | tms.e.group_id           |    1 |   100.00 | NULL                     |
|  1 | PRIMARY            | d     | NULL       | eq_ref | PRIMARY                                                                                     | PRIMARY                                 | 4       | tms.e.department_id      |    1 |   100.00 | NULL                     |
|  1 | PRIMARY            | ewt   | NULL       | ref    | tms_emp_workschedule_reference_no_index,workschedule_date                                   | tms_emp_workschedule_reference_no_index | 767     | tms.c.emp_no             |   83 |   100.00 | Using where              |
|  1 | PRIMARY            | ew    | NULL       | eq_ref | PRIMARY                                                                                     | PRIMARY                                 | 4       | tms.ewt.workschedule_id  |    1 |   100.00 | NULL                     |
|  1 | PRIMARY            | egwt  | NULL       | ref    | tms_emp_workschedule_reference_no_index,workschedule_date                                   | tms_emp_workschedule_reference_no_index | 767     | tms.g.code               |   83 |   100.00 | Using where              |
|  1 | PRIMARY            | egw   | NULL       | eq_ref | PRIMARY                                                                                     | PRIMARY                                 | 4       | tms.egwt.workschedule_id |    1 |   100.00 | NULL                     |
|  2 | DEPENDENT SUBQUERY | dr    | NULL       | ref    | tms_door_record_raw_card_no_index,tms_door_record_raw_record_time_index,record_time_card_no | record_time_card_no                     | 767     | tms.c.card_no            |  266 |     1.27 | Using where; Using index |
+----+--------------------+-------+------------+--------+---------------------------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+--------------------------+
Improve this question
1
  • Are all LEFT JOIN:s necessary or can some of them be replaced with INNER JOIN:s
    – Lennart - Slava Ukraini
    Dec 6, 2016 at 19:07

2 Answers 2

Reset to default
11

The following should help with the execution time:

  • remove the ORDER BY if it isn't strictly necessary
  • replace the join of dr table with WHERE EXISTS (SELECT 1 FROM tms_door_record_raw As dr WHERE c.card_no = dr.card_no AND dr.record_time BETWEEN '2016-11-01' AND '2016-11-02')
  • GROUP BY, may now not be needed
  • widen the index on tms_door_record_raw to include both card_no and record_time

Test this and see if progress is being made. Further steps may be necessary but hopefully this is in the right direction.

Improve this answer
1
1

Remove the GROUP BY.
If you have (logicaly valid) duplicates remove them at early stage.

Improve this answer
2
  • sure, i will update you again on the sql execution time once I remove the index from current table. However I couldn't remove the group by c.card_no, due to tms_door_record_raw table have multiple record with single card no. example, card 0001 have few in/out records in 1 day, so it will show multiple record where I only need if the card having record in that day.
    – cww
    Dec 1, 2016 at 15:25
  • Thank you. I suggest trying to the aggregation on tms_door_record_raw before the joins.
    – David דודו Markovitz
    Dec 1, 2016 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.