Lentitud en consulta con select 1

[Pelirr]

Aquí va la optimización que intenté con subconsulta:

Código MySQL:

select
 
    if (subconsulta.idArrival is null, null, subconsulta.idArrival) as idArrival,
    if (subconsulta.idDeparture is null, null, subconsulta.idDeparture) as idDeparture,
    if (subconsulta.estadoL is null, 'IBK', subconsulta.estadoL) as estadoL,
    if (subconsulta.estadoS is null, 'OBK', subconsulta.estadoL) as estadoS,
    if (subconsulta.aeronave is null, '- -', subconsulta.aeronave) as aeronave,
    if (subconsulta.matricula is null, '- -', subconsulta.matricula) as matricula,
    if (subconsulta.inBlockTime is null, '- -', cast(DATE_FORMAT(subconsulta.inBlockTime, '%d/%m/%Y %H:%i') as char)) as inBlockTime,
    if (subconsulta.aeropuertoL is null, '- -', subconsulta.aeropuertoL) as aeropuertoL,
    if (subconsulta.companiaL is null, '- -', subconsulta.companiaL) as companiaL,
    if (subconsulta.numVueloL is null, '- -', subconsulta.numVueloL) as numVueloL,
    if (subconsulta.paxL is null, '- -', cast(subconsulta.paxL as char)) as paxL,
    if (subconsulta.offBlockTime is null, '- -', cast(DATE_FORMAT(subconsulta.offBlockTime, '%d/%m/%Y %H:%i') as char)) as offBlockTime,
    if (subconsulta.aeropuertoS is null, '- -', subconsulta.aeropuertoS) as aeropuertoS,
    if (subconsulta.companiaS is null, '- -', subconsulta.companiaS) as companiaS,
    if (subconsulta.numVueloS is null, '- -', subconsulta.numVueloS) as numVueloS,
    if (subconsulta.paxS is null, '- -', cast(subconsulta.paxS as char)) as paxS,
    if (subconsulta.airbridgeUse is null, 'NO', if (subconsulta.airbridgeUse='- -', '- -', 'SI')) as subconsulta.airbridgeUse
from
(select
    a.idArrivalFlight as idArrival,
    a.idDepartureFlight as idDeparture,
    a.statusCode as estadoL,
    (select d.statusCode from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight) as estadoS,
    (select c.iataAircraft from `subtypeiataaircraft` c, `aircraft` b where b.subtypeIataCode = c.idSubtypeIata and a.tailNumber = b.tailNumber) as aeronave,
    a.tailNumber as matricula,
    a.estimatedTime as inBlockTime,
    a.originAirport as aeropuertoL,
    a.iataCompanyCode as companiaL,
    a.flightNumber as numVueloL,
    (select c.pax from `paxmerchandisepost` c where c.idArrivalFlight = a.idArrivalFlight) as paxL,
    (select d.estimatedTime from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight) as offBlockTime,
    (select d.iataAirportCode from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight) as aeropuertoS,
    (select d.iataCompanyCode from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight) as companiaS,
    (select d.flightNumber from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight) as numVueloS,
    (select d.idDepartureFlight from `departureflight` d where a.idDepartureFlight = d.idDepartureFlight) as paxS,
    (select e.idFingerService from `fingerservice` e where e.idArrivalFlight = a.idArrivalFlight group by e.idArrivalFlight) as airbridgeUse
   
    from `arrivalflight` a where a.estimatedTime >= '2012-10-16 00:00:00' and a.estimatedTime < '2012-10-17 00:00:00' and a.idSituation = '2'
 
    union
    (select
        (select a.idArrivalFlight from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight) as idArrival,
        e.idDepartureFlight as idDeparture,
        (select a.statusCode from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight) as estadoL,
        e.statusCode as estadoS,
        (select c.iataAircraft from `aircraft` b, `subtypeiataaircraft` c where b.tailNumber = e.tailNumber and b.subtypeIataCode = c.idSubtypeIata) as aeronave,
        e.tailNumber as matricula,
    (select a.estimatedTime from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight) as inBlockTime,
    (select a.originAirport from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight) as aeropuertoL,
    (select a.iataCompanyCode from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight) as companiaL,
    (select a.flightNumber from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight) as numVueloL,
    (select c.pax from `paxmerchandisepost` c where c.idArrivalFlight in (select a.idArrivalFlight from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight)) as paxL,
        e.estimatedTime as offBlockTime,
        e.iataAirportCode as aeropuertoS,
        e.iataCompanyCode as companiaS,
        e.flightNumber as numVueloS,
        (select c.pax from `paxmerchandisepost` c where c.idDepartureFlight = e.idDepartureFlight) as paxS,
        '- -' as airbridgeUse
            from `departureflight` e where
                e.idDepartureFlight not in (select a.idDepartureFlight from `arrivalflight` a where a.idDepartureFlight is not null and a.estimatedTime >= '2012-10-16 00:00:00' and a.estimatedTime < '2012-10-17 00:00:00')
                and e.estimatedTime >= '2012-10-16 00:00:00' and e.estimatedTime < '2012-10-17 00:00:00' and e.idSituation = '2')
 
    order by inBlockTime desc, offBlockTime desc) as subconsulta;
 

Un saludo