Lentitud en consulta con select 1

[Pelirr]

Buenos días, necesito ayuda con una consulta que estoy realizando sobre unas cuantas tablas. El problema es que cuando hay muchos registros, la consulta puede durar hasta 5 minutos, y eso es demasiado, me gustaría optimizar la consulta, pero no sé como hacerlo. He estado mirando por internet, pero igual dicen que hay que utilizar 'inner join' que dicen que es lo mismo utilizar la cláusula 'where', así que estoy hecha un lío. Mi consulta es la siguiente:

Código MySQL:

select
    a.idArrivalFlight as idArrival,
    a.idDepartureFlight as idDeparture,
    a.statusCode as estadoL,
    if ((select d.statusCode from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight) is null, 'OBK', (select d.statusCode from departureflight` d where d.idDepartureFlight = a.idDepartureFlight)) as estadoS,
    if ((select c.iataAircraft from `subtypeiataaircraft` c,`aircraft` b where b.subtypeIataCode = c.idSubtypeIata and a.tailNumber = b.tailNumber) is null, '- -', (select c.iataAircraft from `subtypeiataaircraft` c,`aircraft` b where b.subtypeIataCode = c.idSubtypeIata and a.tailNumber = b.tailNumber)) as aeronave,
    if (a.tailNumber is null, '- -', a.tailNumber) as matricula,
    cast(DATE_FORMAT(a.estimatedTime, '%d/%m/%Y %H:%i') as char) as inBlockTime,
    a.originAirport as aeropuertoL,
    a.iataCompanyCode as companiaL,
    a.flightNumber as numVueloL,
    if ((select c.pax from `paxmerchandisepost` c where c.idArrivalFlight = a.idArrivalFlight) is null, '- -', cast((select c.pax from `paxmerchandisepost` c where c.idArrivalFlight = a.idArrivalFlight) as char)) as paxL,
    if ((select d.estimatedTime from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight) is null, '- -', cast(DATE_FORMAT((select d.estimatedTime from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight),'%d/%m/%Y %H:%i') as char)) as offBlockTime,
    if ((select d.iataAirportCode from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight) is null, '- -', (select d.iataAirportCode from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight)) as aeropuertoS,
    if ((select d.iataCompanyCode from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight) is null, '- -', (select d.iataCompanyCode from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight)) as companiaS,
    if ((select d.flightNumber from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight) is null, '- -', (select d.flightNumber from `departureflight` d where d.idDepartureFlight = a.idDepartureFlight)) as numVueloS,
    if( (select d.idDepartureFlight from `departureflight` d where a.idDepartureFlight = d.idDepartureFlight) is null,  '- -',
        if ((select c.pax from `paxmerchandisepost` c where c.idDepartureFlight = a.idDepartureFlight) is null, '- -',cast((select c.pax from `paxmerchandisepost` c where c.idDepartureFlight = a.idDepartureFlight) as char)))  as paxS,
    if ((select e.idFingerService from `fingerservice` e where e.idArrivalFlight = a.idArrivalFlight group by e.idArrivalFlight) is null, 'NO', 'YES') 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
        if ((select a.idArrivalFlight from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight) is null, null, (select a.idArrivalFlight from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight)) as idArrival,
        e.idDepartureFlight as idDeparture,
        if ((select a.statusCode from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight) is null, 'IBK', (select a.statusCode from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight)) as estadoL,
        e.statusCode as estadoS,
        if ((select c.iataAircraft from `aircraft` b, `subtypeiataaircraft` c where b.tailNumber = e.tailNumber and b.subtypeIataCode = c.idSubtypeIata) is null, '- -', (select c.iataAircraft from `aircraft` b, `subtypeiataaircraft` c where b.tailNumber = e.tailNumber and b.subtypeIataCode = c.idSubtypeIata)) as aeronave,
if (e.tailNumber is null, '- -', e.tailNumber) as matricula,
if ((select a.estimatedTime from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight) is null, '- -',
    cast(DATE_FORMAT((select a.estimatedTime from `arrivalflight` a where  a.idDepartureFlight = e.idDepartureFlight), '%d/%m/%Y %H:%i') as char) ) as inBlockTime,
if ((select a.originAirport from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight) is null, '- -',
    (select a.originAirport from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight)) as aeropuertoL,
if ((select a.iataCompanyCode from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight) is null, '- -',
    (select a.iataCompanyCode from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight)) as companiaL,
if ((select a.flightNumber from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight) is null, '- -',
    (select a.flightNumber from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight)) as numVueloL,
if ((select c.pax from `paxmerchandisepost` c where c.idArrivalFlight in (select a.idArrivalFlight from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight)) is null, '- -', cast((select c.pax from `paxmerchandisepost` c where c.idArrivalFlight in (select a.idArrivalFlight from `arrivalflight` a where a.idDepartureFlight = e.idDepartureFlight)) as char)) as paxL,
        cast(DATE_FORMAT(e.estimatedTime,'%d/%m/%Y %H:%i') as char) as offBlockTime,
        e.iataAirportCode as aeropuertoS,
        e.iataCompanyCode as companiaS,
        e.flightNumber as numVueloS,
        if ((select c.pax from `paxmerchandisepost` c where c.idDepartureFlight = e.idDepartureFlight) is null, '- -',cast((select c.pax from `paxmerchandisepost` c where c.idDepartureFlight = e.idDepartureFlight) as char)) 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;
 

he intentado optimizarla haciendo una subconsulta, porque me parece que elimino algunos select, pero parece que sólo quito unos pocos segundos de consulta, necesito reducir mucho más el tiempo. Os pongo lo que hice en el siguiente mensaje porque no me cabe todo en éste.

No os fijéis en las fechas, son sólo para probar.

Me gustaría saber qué puedo hacer para que vaya más rápido.

Muchas gracias de antemano, un saludo

[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

[Heimish2000]

Para empezar, debes usar JOINs, también poner las condiciones en los where de más restrictiva a más general y luego puedes intentar evitar las subconsultas y también crear índices en las tambas

[Pelirr]

Muchas gracias Heimish2000, lo intentaré. Pero en cuanto a lo de los joins, ¿son más eficientes que la cláusula where? Por lo que he visto por internet, en muchos sitios dice que son por el estilo e incluso mejor utilizar el where que el join. ¿Realmente me aconsejas utilizar el join en lugar del where?

También estoy utilizando 'explain' para ver como ir optimizando.

Muchas gracias, un saludo

[Pelirr]

Hola, he probado a indexar los campos por los que hacía la búsqueda (estimatedTime) en ambas tablas, y ahora la consulta es un 96% más rápida.
Te agradezco mucho Heimish2000 que me respondieras, entre tu respuesta y consultando por internet, utilizando por ejemplo la partícula 'explain' antes de la consulta para ver tb como cambiaba la cosa, me ha ido muy bien.

Muchas gracias, un saludo

[Heimish2000]

Iniciado por Pelirr

Muchas gracias Heimish2000, lo intentaré. Pero en cuanto a lo de los joins, ¿son más eficientes que la cláusula where? Por lo que he visto por internet, en muchos sitios dice que son por el estilo e incluso mejor utilizar el where que el join. ¿Realmente me aconsejas utilizar el join en lugar del where?

También estoy utilizando 'explain' para ver como ir optimizando.

Muchas gracias, un saludo

Ya me dirás dónde pone que son parecidos, porque no es verdad. Si buscas un poco en internet verás que los JOINs son muchísimo más eficientes.

[gnzsoloyo]

Francamente, Pelirr, pocas veces he visto una consulta escrita de una forma más ineficiente, enredada, y con tanto desperdicio de recursos.
Me llevó casi media hora limpiarle todo lo que tiene de más, y no me puse a analizar completamente la lógica de algunas partes, que estimo son completamente inútiles, pero luego de simplificarla quedó así:

Código MySQL:

SELECT
    IFNULL(SC.idArrival, NULL, SC.idArrival) idArrival,
    IFNULL(SC.idDeparture, NULL, SC.idDeparture) idDeparture,
    IFNULL(SC.estadoL, 'IBK', SC.estadoL) estadoL,
    IFNULL(SC.estadoS, 'OBK', SC.estadoL) estadoS,
    IFNULL(SC.aeronave, '- -', SC.aeronave) aeronave,
    IFNULL(SC.matricula, '- -', SC.matricula) matricula,
    IFNULL(SC.inBlockTime, '- -', cast(DATE_FORMAT(SC.inBlockTime, '%d/%m/%Y %H:%i') as CHAR)) inBlockTime,
    IFNULL(SC.aeropuertoL, '- -', SC.aeropuertoL) aeropuertoL,
    IFNULL(SC.companiaL, '- -', SC.companiaL) companiaL,
    IFNULL(SC.numVueloL, '- -', SC.numVueloL) numVueloL,
    IFNULL(SC.paxL, '- -', cast(SC.paxL as CHAR)) paxL,
    IFNULL(SC.offBlockTime, '- -', cast(DATE_FORMAT(SC.offBlockTime, '%d/%m/%Y %H:%i') as CHAR)) offBlockTime,
    IFNULL(SC.aeropuertoS, '- -', SC.aeropuertoS) aeropuertoS,
    IFNULL(SC.companiaS, '- -', SC.companiaS) companiaS,
    IFNULL(SC.numVueloS, '- -', SC.numVueloS) numVueloS,
    IFNULL(SC.paxS, '- -', cast(SC.paxS as CHAR)) paxS,
    IFNULL(SC.airbridgeUse, 'NO', if (SC.airbridgeUse='- -', '- -', 'SI')) airbridgeUse
FROM
    (SELECT
        a.idArrivalFlight idArrival,
        a.idDepartureFlight idDeparture,
        a.statusCode estadoL,
        d.statusCode estadoS,
        ac.iataAircraft aeronave,
        a.tailNumber matricula,
        a.estimatedTime inBlockTime,
        a.originAirport aeropuertoL,
        a.iataCompanyCode companiaL,
        a.flightNumber numVueloL,
        pm.pax paxL,
        d.estimatedTime offBlockTime,
        d.iataAirportCode aeropuertoS,
        d.iataCompanyCode companiaS,
        d.flightNumber numVueloS,
        d.idDepartureFlight paxS,
        fs.idFingerService airbridgeUse
    FROM `arrivalflight` a 
        INNER JOIN `departureflight` d ON a.idDepartureFlight = d.idDepartureFlight
        INNER JOIN `aircraft` ac ON a.tailNumber = ac.tailNumber
        INNER JOIN `subtypeiataaircraft` stc ON ac.subtypeIataCode = ac.idSubtypeIata
        INNER JOIN `paxmerchandisepost` pm INNER JOIN pm.idArrivalFlight = a.idArrivalFlight
        INNER JOIN (SELECT * FROM `fingerservice` GROUP BY e.idArrivalFlight) fs ON e.idArrivalFlight = a.idArrivalFlight
    WHERE 
        a.estimatedTime BETWEEN '2012-10-16 00:00:00' AND '2012-10-16 23:59:59' 
        AND a.idSituation = '2'
     UNION
    (SELECT
        a.idArrivalFlight idArrival,
        e.idDepartureFlight idDeparture,
        a.statusCode estadoL,
        e.statusCode estadoS,
        c.iataAircraft aeronave,
        e.tailNumber matricula,
        a.estimatedTime inBlockTime,
        a.originAirport aeropuertoL,
        a.iataCompanyCode companiaL,
        a.flightNumber numVueloL,
        c.pax paxL,
        e.estimatedTime offBlockTime,
        e.iataAirportCode aeropuertoS,
        e.iataCompanyCode companiaS,
        e.flightNumber numVueloS,
        d.pax paxS,
        '- -' airbridgeUse
    FROM `departureflight` e 
        INNER JOIN `arrivalflight` a ON e.idDepartureFlight = a.idDepartureFlight
        INNER JOIN `aircraft` b ON e.tailNumber = b.tailNumber 
        INNER JOIN `subtypeiataaircraft` c ON b.subtypeIataCode = c.idSubtypeIata
        INNER JOIN `paxmerchandisepost` d ON a.idArrivalFlight = d.idArrivalFlight
    WHERE
            (a.estimatedTime BETWEEN '2012-10-16 00:00:00' AND '2012-10-16 23:59:59')
        AND (e.estimatedTime BETWEEN '2012-10-16 00:00:00' AND '2012-10-16 23:59:59')
        AND e.idSituation = '2'
     ORDER BY inBlockTime desc, offBlockTime desc
     ) SC2;
 

- Eliminé todos los AS, que son completamente innecesarios.
- Cambié el enorme IF de los campos por el más eficiente ISNULL(), que cubre casi toda la necesidad.
- Eliminé esas subconsultas sobre el SELECT, que generan un enorme despedicio de recursos (un subselect de ese tipo se ejecuta una vez por cada registro devuelto, y tu los estás poniendo en la mayoría de los campos... haz la cuenta del costo).
- Simplifiqué las subconsultas en los INNER JOIN que de todos modos son lo que en realida estarías haciendo, pero más eficiente.

No puedo probarlo, porque no tengo los datos para hacerlo, pero en esencia ese sería el modo de hacerlo.
El refinamiento de la consulta te lo dejo a ti.