Optimize this mysql query

Question

Can some one 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 nested query or is it fine in this case? Also suggest me a cakephp solution but the tables can't 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 ALL will not check for duplicates. Always put the ORDER BY clause in the outer query to ensure proper ordering.

It would be better to avoid using UNION because it will not allow the query optimizer to use any index you may have on dinsp and date_of_inspection. But that would mean changing the schema.

Answer 2

An alternative to a UNION sub-query is to make the main query into two parts with a UNION between:

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

It is not clear that this would give significantly different performance. If anything, it would be worse since it involves two scans of the Inspectors table, but that isn't likely to be very big so it may not matter very much. Your UNION sub-query minus the ORDER BY is likely to be as good as or slightly better than this. Your ORDER BY on a non-selected field is problematic in the inner query; and needs careful handling in the UNION I'm proposing (probably by selecting the extra column).

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;