优化此mysql查询

Question

有人可以优化这个mysql查询吗？

SELECT submittedform.*, inspectors.first_name, inspectors.last_name
  FROM (
       SELECT `dinsp`,`departure`,`arrival`,'cabin' as type FROM cabinets 
       UNION
       SELECT `dinsp`,`departure`,`arrival`,'cockpit' as type FROM cockpits
       ORDER BY `date_of_inspection` ASC
       ) AS submittedform 
       INNER JOIN inspectors ON inspectors.id = submittedform.dinsp

我不想依赖嵌套查询，或者在这种情况下可以吗？还建议我一个cakephp解决方案，但表不能相关。

Answer 1

您可以尝试：

SELECT sf.`dinsp`, sf.`departure`, sf.`arrival`, sf.`type`, i.`first_name`, i.`last_name`
FROM
    `inspectors` AS i INNER JOIN (
    SELECT `dinsp`, `departure`, `arrival`, `date_of_inspection`, 'cabin' AS `type`
    FROM `cabinets`
    UNION ALL
    SELECT `dinsp`, `departure`, `arrival`, `date_of_inspection`, 'cockpit' AS `type`
    FROM `cockpits`
) AS sf ON sf.`dinsp` = i.`id`
ORDER BY sf.`date_of_inspection`

UNION ALL不会检查重复项。始终将ORDER BY子句放在外部查询中，以确保正确的排序。

最好避免使用UNION，因为它不允许查询优化器使用dinsp和date_of_inspection上的任何索引。但这将意味着更改模式。

Answer 2

UNION子查询的另一种选择是将主查询分成两个部分，其间使用UNION：

SELECT c.dinsp, c.departure, d.arrival, 'cabin'   AS type, i.first_name, i.last_name
  FROM cabinets AS c JOIN inspectors AS i ON i.id = c.dinsp
SELECT c.dinsp, c.departure, d.arrival, 'cockpit' AS type, i.first_name, i.last_name
  FROM cockpits AS c JOIN inspectors AS i ON i.id = c.dinsp

目前还不清楚这是否会带来显着不同的性能。如果有什么不同的话，那就是更糟了，因为它涉及到对检查员表的两次扫描，但这不太可能很大，所以它可能不是很重要。您的UNION子查询减去ORDER BY可能与此一样好，甚至稍好一些。未选中字段上的ORDER BY在内部查询中是有问题的；并且需要在我建议的UNION中小心处理(可能是通过选择额外的列)。

SELECT c.dinsp, c.date_of_inspection, c.departure, d.arrival, 'cabin'   AS type,
       i.first_name, i.last_name
  FROM cabinets AS c JOIN inspectors AS i ON i.id = c.dinsp
SELECT c.dinsp, c.date_of_inspection, c.departure, d.arrival, 'cockpit' AS type,
       i.first_name, i.last_name
  FROM cockpits AS c JOIN inspectors AS i ON i.id = c.dinsp
 ORDER BY date_of_inspection;