Optimize this mysql query

Question

Is it possible to optimize this mysql query

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

I don't want to rely on a subquery or is everything okay? Also suggest me a cakephp solution, but tables cannot be related.

Answer 1

You can try:

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 ALLwill not check for duplicates. Always put an offer ORDER BYin an external request to ensure proper ordering.

It is better to avoid using UNIONit because it will not allow the query optimizer to use any index you may have on dinspand date_of_inspection. But that would mean a circuit change.

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;