优化这个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解决方案，但是这些表不能相关联。

- aWebDeveloper

---

ORDER BY子句非常奇怪 - 可能可以删除。 - Jonathan Leffler

CakePHP不支持UNION，因为它只是MySQL的一个功能。但是你可以使用dbo生成查询，然后在中间添加union，例如：http://tinyurl.com/34olua5。 - dogmatic69

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 上拥有的任何索引。但这意味着需要更改模式。

- Xint0

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

不清楚这是否会带来显著不同的性能。如果有什么区别，那么它会更糟糕，因为它涉及两次扫描Inspectors表，但这可能并不重要，因为这个表不太可能很大。您提出的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;

- Jonathan Leffler