Performance queries con múltiples JOIN.

[Carlangueitor]

Hola,

Tengo una consulta, no muy grande y hasta donde entiendo no debería ser tan ineficaz. Actualmente tarda ~110s, la tabla no contiene muchos registros (200k), lo que supongo que el problema está en el subquery, ¿alguna sugerencia?

Código MySQL:

SELECT 
    auctions_flight.id,
    auctions_flight.`date`,
    origins.code as origin_code,
    origins.name as origin,
    destinations.name as destination,
    destinations.code as destination_code,
    auctions_flight.num_seating,
    auctions_flightprice.Price,
    auctions_flightprice.currency
FROM
    auctions_flight
        INNER JOIN
    auctions_flightprice ON auctions_flight.price_id = auctions_flightprice.id
        INNER JOIN
    locations_location as destinations ON auctions_flight.destination_location_id = destinations.id
        INNER JOIN
    locations_location as origins ON auctions_flight.origin_location_id = origins.id
WHERE
    auctions_flight.num_seating >= 3
        AND origins.code = 'MEX'
        AND auctions_flightprice.currency = 'MXN'
        AND auctions_flightprice.Price = (SELECT 
            MIN(Price)
        FROM
            auctions_flightprice
        WHERE
            origin_location_id = auctions_flight.origin_location_id
                AND destination_location_id = auctions_flight.destination_location_id
                AND `date` = auctions_flight.`date`)
ORDER BY auctions_flightprice.`Price`
LIMIT 25;
 

Saludos.

[quimfv]

Código MySQL:

SELECT auctions_flight.id,
    auctions_flight.`date`,
    origins.code as origin_code,
    origins.name as origin,
    destinations.name as destination,
    destinations.code as destination_code,
    auctions_flight.num_seating,
    auctions_flightprice.Price,
    auctions_flightprice.currency
FROM auctions_flight
        INNER JOIN
    auctions_flightprice ON auctions_flight.price_id = auctions_flightprice.id
        INNER JOIN
    locations_location as destinations ON auctions_flight.destination_location_id = destinations.id
        INNER JOIN
    locations_location as origins ON auctions_flight.origin_location_id = origins.id
        INNER JOIN
    (SELECT origin_location_id,
                destination_location_id,
                `date`,
                MIN(Price) MPrice
        FROM auctions_flightprice
        GROUP BY origin_location_id,
                destination_location_id,
                `date`
    ) Sbc
        ON auctions_flightprice.Price =Sbc.MPrice
            AND Sbc.origin_location_id = auctions_flight.origin_location_id
            AND Sbc.destination_location_id = auctions_flight.destination_location_id
            AND Sbc.`date` = auctions_flight.`date`
WHERE auctions_flight.num_seating >= 3
    AND origins.code = 'MEX'
    AND auctions_flightprice.currency = 'MXN'
ORDER BY auctions_flightprice.`Price`
LIMIT 25;
 

Esto correra mas y creo que debe dar lo mismo....

(Creo)

[Carlangueitor]

Gracias! Efectivamente trabaja mucho más rápido, tarda ~20s, lo cuál tampoco es muy aceptable. Pero me di cuenta (por mi poco conocimiento en bases de datos y SQL) de otra manera de hacerlo.

Código MySQL:

SELECT 
    auctions_flight.id,
    auctions_flight.date,
    destinations.code as destination_code,
    destinations.name as destination,
    MAX(auctions_flight.num_seating) as num_seating,
    auctions_flightprice.Price,
    MIN(auctions_flightprice.Price) as price
FROM
    auctions_flight
        INNER JOIN
    auctions_flightprice ON auctions_flight.price_id = auctions_flightprice.id
        INNER JOIN
    locations_location AS origins ON auctions_flight.origin_location_id = origins.id
        INNER JOIN
    locations_location AS destinations ON auctions_flight.destination_location_id = destinations.id
WHERE
    auctions_flight.num_seating >= 3
        AND auctions_flight.price_id IS NOT NULL
        AND auctions_flightprice.currency = 'MXN'
        AND origins.code = 'MEX'
        AND auctions_flight.date >= CURDATE()
GROUP BY auctions_flight.date , auctions_flight.destination_location_id , auctions_flight.origin_location_id
ORDER BY price , auctions_flight.date
LIMIT 0 , 25;
 

Y hasta ahora funciona adecuadamente y a buen tiempo ~0.1s.


Saludos

[quimfv]

Y auctions_flight.id siempre corresponde a MIN(auctions_flightprice.Price) as price ?

[Carlangueitor]

¿A que te refieres?

Lo que quiero lograr es de la auctions_flight obtener un vuelo por cada combinación de fecha, origen y destino, obtener el precio más bajo y obtener el número de asientos más alto de los que cumplen la combinación.

Hasta donde entiendo si es correcto como lo manejo, ¿no?

Saludos

[quimfv]

auctions_flight.id corresponde a un vuelo concreto

MIN(auctions_flightprice.Price) es el precio mínimo de los vuelos que cumplen las condiciones de busqueda.

Y ahora repregunto

Y auctions_flight.id siempre corresponde a MIN(auctions_flightprice.Price) as price ?

[Carlangueitor]

Ahh ya, realmente no importa el ID, por lo menos no para este caso, ya que justamente quiero obtener solo los datos del vuelo que cumpla las condiciones para mostrarlos en una lista de resultados, luego tomando esos mismos parámetros se hacen las operaciones siguientes si el usuario requiere.

Saludos

[quimfv]

??? tu sabras.... pero las operaciones deben hacerse sobre un vuelo concreto ... el del id si el id y el precio no corresponden ....????

[Carlangueitor]

No del todo. Te explico un poco como funciona. Muestro los resultados para obtener el precio más bajo, luego, si se selecciona comprar, yo mando a otro sistema los parámetros de búsqueda (origen, destino, y fecha), y ese sistema ya da todas las opciones de precio y asientos disponibles con detalle. En este sistema solo se requieren obtener datos para el usuario.


Saludos

[gnzsoloyo]

Pregunta: ¿Has analizado la selectiividad que se aplica en cada tabla del JOIN?
Es decir, cuando no puedes evitar hacer un JOIN múltiple, hay que tratar de hacer que la cadena de tablas derivadas que se genera (de izquierda a derecha) vaya generando la menor cantidad de resultados.
digamos así, si sabemos que la cuarta tabla tiene alta selectividad (bajo nivel de resultados), y la segunda baja selectividad (alto nivel de resultados), es mejor cambiar el orden para que se reduzca la salida de datos.
En esencia, en ciertas condiciones el orden de los factores sí altera el producto...

[Carlangueitor]

No lo había analizado, creo que entonces debería pasar al último el primer JOIN. Reviso a ver que obtengo.

Saludos!