Estou trabalhando em um aplicativo php e preciso recuperar o registro mais recente de uma tabela mysql. Abaixo está o design da tabela e os dados de amostra.

Então o critério é:

classifique por DESC presente e, em seguida, start_date DESC

Saída esperada

Estou pensando como abaixo, existe alguma solução melhor sobre isso?

SELECT * 
  FROM `experience` r 
  JOIN (
        SELECT MAX(start_date) as date, 
               resume_id 
          FROM `experience` 
         WHERE present = 1 
         group by resume_id
        )r2 ON r.resume_id = r2.resume_id 
           AND r.present = 1 
           AND r.start_date = r2.date  
UNION
SELECT * 
  FROM `experience` r 
  JOIN (
        SELECT MAX(start_date) as date, 
               resume_id 
          FROM `experience` 
         WHERE present = 0 
         group by resume_id
       ) r2 ON r.resume_id = r2.resume_id 
           AND r.present = 0 
           AND r.start_date = r2.date  
 WHERE NOT EXISTS (SELECT 1 
                     FROM `experience` 
                    WHERE present = 1 
                      AND r.resume_id = resume_id
                  )

// took around 11-20 seconds to process above query on total 499,000 records

Explicar a consulta

ATUALIZAR:

http://sqlfiddle.com/#!9/bc5a12/1

CREATE TABLE `experience`(
       `id` int(11) NOT NULL,
       `resume_id` varchar(9) DEFAULT NULL,
       `company_name` varchar(12) DEFAULT NULL,
       `start_date` date DEFAULT NULL,
       `end_date` date DEFAULT NULL,
       `present` varchar(7) DEFAULT NULL
       ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `experience`(
       `id`, 
       `resume_id`,  
       `company_name`, 
       `start_date`, 
       `end_date`, 
       `present`) 
 VALUES
(1, '1', 'abc', '2009-01-01', '2009-12-31', '0'),
(2, '1', 'def', '2010-01-01', '2012-12-31', '0'),
(3, '1', 'ghi', '2013-01-01', '0000-00-00', '1'),
(4, '2', 'abc', '2009-01-01', '2009-12-31', '0'),
(5, '2', 'def', '2010-01-01', '2012-12-31', '0'),
(6, '2', 'ghi', '2013-01-01', '2016-01-01', '0'),
(7, '3', 'abc', '2009-01-01', '2009-12-31', '0'),
(8, '3', 'def', '2017-01-01', '0000-00-00', '1'),
(9, '3', 'ghi', '2010-01-01', '0000-00-00', '1');

ALTER TABLE `experience`
  ADD PRIMARY KEY (`id`);

Eu tenho uma subconsulta lenta como abaixo (levando 2 minutos e 35,03 segundos)

SELECT *
FROM erp_gl_batch_item i
LEFT JOIN erp_gl_batch b
  ON i.gl_batch_id = b.gl_batch_id
LEFT JOIN erp_customer c
  ON i.gl_item_account_no = c.customer_code
LEFT JOIN (SELECT *
    FROM erp_acc_match_invoice mi
    WHERE mi.allocation = 'full') mi
      ON i.gl_reference = mi.reference
LEFT JOIN (SELECT *
    FROM erp_acc_match_invoice mip
    WHERE mip.allocation = 'partial') mip
      ON i.gl_reference = mip.reference
WHERE i.gl_item_account_no LIKE '3001/A01'
AND mip.match_invoice_id IS NULL
AND b.gl_period <= '2016012'
AND i.gl_item_debit <> '0.00'
AND mi.match_invoice_id IS NULL;
89 rows in set (2 min 35.03 sec)

Abaixo está a saída de explicação.

+----+-------------+------------+--------+--------------------------------+--------------------+---------+------------------------------------+-------+-------------------------+
| id | select_type | table      | type   | possible_keys                  | key                | key_len | ref                                | rows  | Extra                   |
+----+-------------+------------+--------+--------------------------------+--------------------+---------+------------------------------------+-------+-------------------------+
|  1 | PRIMARY     | i          | range  | gl_item_account_no,gl_batch_id | gl_item_account_no | 767     | NULL                               |  4713 | Using where             |
|  1 | PRIMARY     | b          | eq_ref | PRIMARY                        | PRIMARY            | 4       | c1xxxtemp_erp.i.gl_batch_id        |     1 | Using where             |
|  1 | PRIMARY     | c          | ref    | customer_code                  | customer_code      | 767     | c1xxxtemp_erp.i.gl_item_account_no |     1 |                         |
|  1 | PRIMARY     | <derived2> | ALL    | NULL                           | NULL               | NULL    | NULL                               | 28965 | Using where; Not exists |
|  1 | PRIMARY     | <derived3> | ALL    | NULL                           | NULL               | NULL    | NULL                               |   149 | Using where; Not exists |
|  3 | DERIVED     | mip        | ref    | allocation                     | allocation         | 33      |                                    |   149 | Using where             |
|  2 | DERIVED     | mi         | ref    | allocation                     | allocation         | 33      |                                    | 15306 | Using where             |
+----+-------------+------------+--------+--------------------------------+--------------------+---------+------------------------------------+-------+-------------------------+

Só quero saber como funciona a subconsulta e junção e como isso afeta o desempenho da consulta. Além disso, é por causa da junção que ele precisa procurar a linha 4.713 x 28.965 = 136.512.045 linhas?

Eu reescrevi a consulta e ela está mais rápida agora (0,12 s), apenas me pergunto se é uma subconsulta também, mas como essa subconsulta funciona mais rápido.

SELECT *
FROM erp_gl_batch_item i
LEFT JOIN erp_gl_batch b
  ON i.gl_batch_id = b.gl_batch_id
LEFT JOIN erp_customer c
  ON i.gl_item_account_no = c.customer_code
WHERE i.gl_item_account_no LIKE '3001/A01'
AND b.gl_period <= '2016012'
AND i.gl_item_debit <> '0.00'
AND NOT EXISTS (SELECT *
    FROM erp_acc_match_invoice mip
    WHERE mip.allocation = 'partial'
    AND i.gl_reference = mip.reference)
AND NOT EXISTS (SELECT *
    FROM erp_acc_match_invoice mi
    WHERE mi.allocation = 'full'
    AND i.gl_reference = mi.reference)

Explique

+----+--------------------+-------+--------+--------------------------------+--------------------+---------+------------------------------------+------+-------------+
| id | select_type        | table | type   | possible_keys                  | key                | key_len | ref                                | rows | Extra       |
+----+--------------------+-------+--------+--------------------------------+--------------------+---------+------------------------------------+------+-------------+
|  1 | PRIMARY            | i     | range  | gl_item_account_no,gl_batch_id | gl_item_account_no | 767     | NULL                               | 4713 | Using where |
|  1 | PRIMARY            | b     | eq_ref | PRIMARY                        | PRIMARY            | 4       | c1xxxtemp_erp.i.gl_batch_id        |    1 | Using where |
|  1 | PRIMARY            | c     | ref    | customer_code                  | customer_code      | 767     | c1xxxtemp_erp.i.gl_item_account_no |    1 |             |
|  3 | DEPENDENT SUBQUERY | mi    | ref    | reference,allocation           | reference          | 767     | c1xxxtemp_erp.i.gl_reference       |    1 | Using where |
|  2 | DEPENDENT SUBQUERY | mip   | ref    | reference,allocation           | reference          | 767     | c1xxxtemp_erp.i.gl_reference       |    1 | Using where |
+----+--------------------+-------+--------+--------------------------------+--------------------+---------+------------------------------------+------+-------------+

Atualizar

CREATE TABLE `erp_gl_batch_item` (
  `gl_batch_item_id` int(11) NOT NULL AUTO_INCREMENT,
  `gl_batch_id` int(11) NOT NULL,
  `gl_line_item` int(11) NOT NULL,
  `gl_item_date` date NOT NULL,
  `gl_item_account_id` int(11) DEFAULT NULL,
  `gl_item_account_no` varchar(255) NOT NULL,
  `gl_reference` varchar(255) NOT NULL,
  `gl_reference2` varchar(255) DEFAULT NULL,
  `gl_tax_code` int(11) DEFAULT NULL,
  `gl_desc` varchar(255) NOT NULL,
  `gl_foreign_currency` varchar(10) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `gl_rate` varchar(10) DEFAULT NULL,
  `gl_item_foreign_amount_debit` decimal(10,2) DEFAULT '0.00',
  `gl_item_foreign_amount_credit` decimal(10,2) NOT NULL DEFAULT '0.00',
  `gl_item_debit` decimal(10,2) DEFAULT '0.00',
  `gl_item_credit` decimal(10,2) DEFAULT '0.00',
  `knockoff` varchar(40) DEFAULT NULL,
  `marked` varchar(10) DEFAULT NULL,
  `gst_log_id` int(11) NOT NULL DEFAULT '0',
  `tax_group_key` int(11) NOT NULL DEFAULT '0',
  `user_created` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `user_modified` int(11) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`gl_batch_item_id`),
  KEY `gl_item_account_id` (`gl_item_account_id`),
  KEY `gl_reference` (`gl_reference`),
  KEY `knockoff` (`knockoff`),
  KEY `marked` (`marked`),
  KEY `gl_item_account_no` (`gl_item_account_no`),
  KEY `gl_batch_id` (`gl_batch_id`)
) ENGINE=InnoDB AUTO_INCREMENT=208712 DEFAULT CHARSET=utf8 


CREATE TABLE `erp_gl_batch` (
  `gl_batch_id` int(11) NOT NULL AUTO_INCREMENT,
  `gl_batch_no` int(11) NOT NULL,
  `gl_batch_name` varchar(255) NOT NULL,
  `gl_period` int(11) DEFAULT NULL,
  `gl_bill_type` int(11) NOT NULL,
  `gl_date_start` date NOT NULL,
  `gl_date_end` date NOT NULL,
  `user_created` int(11) DEFAULT NULL,
  `user_modified` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`gl_batch_id`)
) ENGINE=InnoDB AUTO_INCREMENT=282 DEFAULT CHARSET=utf8

CREATE TABLE `erp_customer` (
  `customer_id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_code` varchar(255) NOT NULL,
  `customer_name` varchar(255) NOT NULL,
  `company_reg_no` varchar(255) DEFAULT NULL,
  `billing_address` text,
  `shipping_address` text,
  `contact_name` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(255) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_fax` varchar(255) DEFAULT NULL,
  `contact_website` varchar(255) DEFAULT NULL,
  `currency_id` int(11) DEFAULT NULL,
  `credit_term_id` int(11) DEFAULT NULL,
  `credit_limit` decimal(16,4) DEFAULT NULL,
  `gst_no` varchar(255) DEFAULT NULL,
  `tax_code_id` int(11) DEFAULT NULL,
  `po_line_required` tinyint(1) DEFAULT NULL,
  `freeze_account` int(11) DEFAULT NULL,
  `status` varchar(255) DEFAULT NULL,
  `status_id` int(11) DEFAULT NULL,
  `remarks` text,
  `user_created` int(11) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `user_modified` int(11) DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL
  PRIMARY KEY (`customer_id`),
  KEY `customer_code` (`customer_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1072 DEFAULT CHARSET=utf8

CREATE TABLE `erp_acc_match_invoice` (
  `match_invoice_id` int(11) NOT NULL AUTO_INCREMENT,
  `log_id` int(11) NOT NULL,
  `chartaccount_id` int(11) NOT NULL,
  `batch_item_id` int(11) NOT NULL,
  `inv_date` date NOT NULL DEFAULT '0000-00-00',
  `reference` varchar(255) NOT NULL,
  `pay_reference` varchar(255) DEFAULT NULL,
  `mi_currency` varchar(10) DEFAULT NULL,
  `mi_rate` varchar(10) DEFAULT NULL,
  `mi_amount` decimal(10,2) NOT NULL,
  `inv_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `allocation` varchar(10) DEFAULT NULL,
  `user_created` int(11) NOT NULL,
  `date_created` datetime NOT NULL,
  `rvknockoff` varchar(40) DEFAULT NULL,
  `inknockoff` varchar(40) DEFAULT NULL,
  `table_location` varchar(10) NOT NULL,
  PRIMARY KEY (`match_invoice_id`),
  KEY `rvknockoff` (`rvknockoff`),
  KEY `inknockoff` (`inknockoff`),
  KEY `reference` (`reference`),
  KEY `pay_reference` (`pay_reference`),
  KEY `log_id` (`log_id`),
  KEY `allocation` (`allocation`)
) ENGINE=InnoDB AUTO_INCREMENT=37368 DEFAULT CHARSET=utf8

A consulta abaixo está demorando mais de 11 minutos para ser executada.

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 

Abaixo está a consulta de explicação

+----+-------------+-------+------------+--------+-------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+---------------------------------+
| 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                            |
+----+-------------+-------+------------+--------+-------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+---------------------------------+

Estrutura da tabela

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

Estou pensando se é o problema "Usando temporário; Usando filesort" ou devo criar um índice de várias colunas, mas não sei como corrigi-lo. Por favor informar.

Atualização 1:

Depois de adicionar o índice de várias colunas na tabela tms_door_record_raw (KEY card_no_record_time( card_no, record_time)) a execução do sql caiu de 11 minutos para 3,2 segundos

Execute o sql de explicação novamente. A chave da tabela de junção drmudou de (card_no) para (card_no, record_time) também na coluna extra que está mostrandoUsing 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                            |
+----+-------------+-------+------------+--------+---------------------------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+---------------------------------+

Atualização 2: Remova o drjoin sql e substitua por where exist condition sugerido por @mendosi altere o tempo de execução para 0,60 segundos.

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;

Abaixo está o sql explicativo

+----+--------------------+-------+------------+--------+---------------------------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+--------------------------+
| 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 |
+----+--------------------+-------+------------+--------+---------------------------------------------------------------------------------------------+-----------------------------------------+---------+--------------------------+------+----------+--------------------------+

    +----+--------------+-----+-----------+----------+
    | ID | NAME         | AGE | ADDRESS   | SALARY   |
    +----+--------------+-----+-----------+----------+
    |  1 | Ramesh Olive |  32 | Ahmedabad |  2000.00 |
    |  2 | Tan Kau      |  25 | Delhi     |  1500.00 |
    |  3 | Jason Tan Kau|  25 | Delhi     |  2000.00 |
    |  4 | Chaitali     |  25 | Mumbai    |  6500.00 |
    |  5 | Hardik       |  27 | Bhopal    |  8500.00 |
    |  6 | Hardik Jass  |  27 | Bhopal    |  4500.00 |
    |  7 | Muffy John   |  24 | Indore    | 10000.00 |
    |  8 | Muffy Lee    |  24 | Indore    | 10000.00 |
    +----+--------------+-----+-----------+----------+

No exemplo acima, vamos dizer que o nome da tabela é "table_a" e 1) "Tan Kau" é duplicado com "Jason Tan Kau" e 2) "Hardik" é duplicado com "Hardik Jass"

Como escrever SQL que produzirá uma saída como abaixo?

Eu acho que isso vai funcionar, mas deve ser muito lento. Alguma ideia para melhorar isso?

Select A.*, IF(B.ID IS NULL, "", "DUP") as DUP
FROM table_a A 
LEFT JOIN table_a B 
ON A.NAME LIKE CONCATE("%", B.NAME, "%") AND A.ID != B.ID


    +----+--------------+-----+-----------+----------+-----+
    | ID | NAME         | AGE | ADDRESS   | SALARY   | DUP |
    +----+--------------+-----+-----------+----------+-----+
    |  1 | Ramesh Olive |  32 | Ahmedabad |  2000.00 |     |
    |  2 | Tan Kau      |  25 | Delhi     |  1500.00 | Dup |
    |  3 | Jason Tan Kau|  25 | Delhi     |  2000.00 | Dup |
    |  4 | Chaitali     |  25 | Mumbai    |  6500.00 |     |
    |  5 | Hardik       |  27 | Bhopal    |  8500.00 | Dup |
    |  6 | Hardik Jass  |  27 | Bhopal    |  4500.00 | Dup | 
    |  7 | Muffy John   |  24 | Indore    | 10000.00 |     |
    |  8 | Muffy Lee    |  24 | Indore    | 10000.00 |     |
    +----+--------------+-----+-----------+----------+-----+

Estou desenvolvendo um software de inventário para varejistas de telefones celulares. Há várias coisas que me confundem.

Pronto, temos:

1. produtos
2. Atributos do produto
3. código de barras
4. Estoque em lote (compras/devoluções, etc)
5. Lote de falta de estoque (amostras/vendas, etc.)
6. Lote de Transferência de Estoque (Transferir estoque do armazém A para B)

Pretendo fazer uma tabela de transações para entrada/saída/transferência de estoque. Portanto, minha tabela de produtos e atributos e código de barras não deve ter um campo de quantidade, certo?

Abaixo estão minhas regras de negócios

    1 product, 0 or many attributes; 
    1 attributes, 1 to many product

    1 product, 0 to many barcode;
    1 barcode, 1 product

    1 attributes, 0 to many barcode;
    1 barcode, 1 attributes

    1 barcode, 0 to many quantity

Quantas tabelas devo criar para isso?

O design da minha mesa é assim:

    - warehouse (warehouse id[pk])
    - product (product id[pk])
    - attributes (attribute id[pk])
    - product attributes (product id[pfk], attribute id[pfk])
    - barcode (barcode id[pk])

    - stock in batch (sib_id[pk], warehouse id[fk])
    - stock in batch item (item_id[pk], sib_id[fk], product id[fk], qty, unit cost)
    - stock in batch item attributes (item_id[pfk], attribute id[pfk])
    - stock in batch item barcode (item_id[pfk], barcode id[pfk])

    - stock out batch (sob_id[pk], warehouse id[fk])
    - stock out batch item (item_id[pk], sob_id[fk], product id[fk], qty, unit cost)
    - stock out batch item attributes (item_id[pfk], attribute id[pfk])
    - stock out batch item barcode (item_id[pfk], barcode id[pfk])

    - stock transfer batch (stb_id[pk], from warehouse id[fk], to warehouse id[fk])
    - stock transfer batch item (item_id[pk], stb_id[fk], product id[fk], qty, unit cost)
    - stock transfer batch item attributes (item_id[pfk], attribute id[pfk])
    - stock transfer batch item barcode (item_id[pfk], barcode id[pfk])

Devo ter o campo qty? Onde devo colocar a quantidade?

Estou pensando em usar sum() na transação de entrada e saída de estoque menos () para obter a quantidade do produto, mas depois de pensar, estou ficando muito complicado e preso aqui.

Também estou pensando em usar o campo qty, para cada transação ele ajustará automaticamente o campo qty, mas temo que o qty final não coincida com o cálculo da transação.

Por favor informar.