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:
Ver original a.idArrivalFlight
as idArrival
, a.idDepartureFlight
as idDeparture
, 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