0

How can I rewrite the query to increase performance?
Here is the query:

SELECT flight.*,
       DAYOFWEEK(flight.flight_time)                            AS day_of_week,
       IF(SUM(m2m_order_flights.price_gross) IS NULL, 0, SUM(
       m2m_order_flights.price_gross))                          AS sells,
       IF(flight.from_order = 0, flight.price_net * IFNULL(flight.seats, 0),
       IFNULL(SUM(m2m_order_flights.price_net), 0))             AS total_net,
       fn_getCountSold(flight.id)                               AS sold,
       fn_getCountReserved(flight.id)                           AS reserved,
       fn_getCountAvailable(flight.id, 434)                     AS available,
       IFNULL((SELECT SUM(of.price_gross)
               FROM   m2m_order_flights `of`
                      INNER JOIN `order` o
                              ON o.id = `of`.order_id
               WHERE  `of`.flight_id = flight.id
                      AND o.deleted_at IS NULL
                      AND `of`.deleted_at IS NULL), 0)          AS amount,
       (SELECT IFNULL(SUM(paid * IF(flight.currency_id =
                                    supplier_voucher.currency_id,
                                 1, (
                                         SELECT
                                                sell_rate
                                 FROM
                                                    currency_rate
                                         WHERE
                                 target_currency_id =
                                 supplier_voucher.currency_id
                                 AND base_currency_id = flight.currency_id
                                 AND current = 1
                                         LIMIT  1))), 0)
        FROM   m2m_psv_flights
               JOIN supplier_voucher
                 ON supplier_voucher.id = m2m_psv_flights.supplier_voucher_id
        WHERE  flight_id = flight.id
               AND supplier_voucher.canceled_at IS NULL
               AND supplier_voucher.deleted_at IS NULL
               AND supplier_voucher.type = 'project')
       + (SELECT IFNULL(SUM(price_net), 0)
          FROM   m2m_order_flights
          WHERE  m2m_order_flights.flight_id = flight.id
                 AND m2m_order_flights.supplier_voucher_id > 0) AS psv_paid
FROM   `flight`
       LEFT JOIN `m2m_order_flights`
              ON `m2m_order_flights`.`flight_id` = `flight`.`id`
WHERE  `flight`.`deleted_at` IS NULL
       AND `flight`.`deleted_at` IS NULL
       AND `flight`.`project_id` = 359
       AND ( fn_isAvailableFlight(flight.id, 434) = 1 )
GROUP  BY flight.id 

And here are the functions:

CREATE DEFINER=`root`@`localhost` FUNCTION `test`.`fn_getCountAvailable`(i_FlightId MEDIUMINT, i_UserId MEDIUMINT) RETURNS mediumint(9)
BEGIN
    DECLARE available_seats INT;
    DECLARE restricted_available_seats INT;
    DECLARE is_allowed INT;
    DECLARE is_admin INT;

    SET @available_seats = (IFNULL((SELECT seats FROM flight WHERE id = i_FlightId), 0) - fn_getCountSold(i_FlightId) - fn_getCountReserved(i_FlightId));

    #SET @is_admin = (SELECT 1 FROM role_user WHERE role_id = 1 AND user_id = i_UserId);

    #IF(@is_admin = 1) THEN
        RETURN @available_seats;
    #END IF;

    #SET @is_allowed = (SELECT IFNULL(1, 0) FROM role_user ru
    #                  INNER JOIN permission_role pr ON pr.role_id = ru.role_id
    #                  INNER JOIN permissions p ON p.id = pr.permission_id
    #                  WHERE user_id = i_UserId AND p.name = 'restricted_to_num_seats');

    #IF(@is_allowed = 0) THEN
    #   RETURN @available_seats;
    #END IF;

    #SET @restricted_available_seats = (SELECT `value` FROM settings WHERE `key` = 'restrict_availible_view_seats');

    #IF(@available_seats >= @restricted_available_seats) THEN
    #  RETURN @restricted_available_seats;
    #ELSE
    #  RETURN @available_seats;
    #END IF;

END;

CREATE DEFINER=`root`@`localhost` FUNCTION `test`.`fn_getCountReserved`(i_FlightId MEDIUMINT) RETURNS mediumint(9)
    DETERMINISTIC
BEGIN
    DECLARE reserved_seats INT;
    SET @reserved_seats = IFNULL((SELECT SUM(seats) FROM reserved_seats WHERE until >= current_date() AND flight_id = i_FlightId AND deleted_at is null), 0);

  RETURN @reserved_seats;

END;

CREATE DEFINER=`root`@`localhost` FUNCTION `test`.`fn_getCountSold`(i_FlightId MEDIUMINT) RETURNS mediumint(9)
BEGIN
    DECLARE sold_flights INT;
    SET @sold_flights = IFNULL((
              SELECT
                COUNT(*) FROM m2m_order_flights `of`
              INNER JOIN `order` o ON o.id = `of`.order_id
              WHERE `of`.flight_id = i_FlightId AND o.status_id IN(1,2,3) AND o.deleted_at is null AND `of`.deleted_at is null), 0);

  RETURN @sold_flights;

END;

CREATE DEFINER=`root`@`localhost` FUNCTION `test`.`fn_isAvailableFlight`(i_FlightId MEDIUMINT, i_AgentId MEDIUMINT) RETURNS tinyint(4)
BEGIN
    DECLARE has_available_flights INT;
    DECLARE flight_seats INT;
    DECLARE flight_reserved_seats INT;
    DECLARE flight_sold_seats INT;
    DECLARE agent_reserved_seats INT;

    SET @flight_seats = (SELECT seats FROM flight WHERE id = i_FlightId);
    SET @flight_reserved_seats = (SELECT IFNULL(SUM(seats), 0) FROM reserved_seats WHERE until >= current_date() AND flight_id = i_FlightId AND deleted_at IS NULL);
    SET @flight_sold_seats = (SELECT fn_getCountSold(i_FlightId));

    SET @has_available_flights = (@flight_seats - @flight_reserved_seats - @flight_sold_seats);

    IF(@has_available_flights > 0) THEN
            RETURN 1;
    END IF;

    SET @agent_reserved_seats = (SELECT IFNULL(SUM(seats), 0) FROM reserved_seats WHERE until >= current_date() AND flight_id = i_FlightId AND agent_id = i_AgentId);

    IF(@agent_reserved_seats > 0) THEN
        RETURN 1;
    ELSE
        RETURN 0;
    END IF;

END;

My the only attempt to optimize this was to move the fn_isAvailableFlight condition into having clause, which increased performance in about 50 times, but it's not enough.
This is the link to mySQL dump for curious.

UPDATE:
Now, thanks to Rick James, the query runs a reasonable amount of time :)

SELECT    flight.*,
          DAYOFWEEK(flight.flight_time)                                                                   AS day_of_week,
          IFNULL(m2m.sells,0)                                                                             AS sells,
          IF(flight.from_order = 0, flight.price_net * IFNULL(flight.seats, 0), IFNULL(m2m_total_net, 0)) AS total_net,
          IFNULL(m2m.sold,0)                                                                              AS sold,
          IFNULL(m2m.reserved,0)                                                                          AS reserved,
          (IFNULL(flight.seats,0)-IFNULL(m2m.reserved,0)-IFNULL(m2m.sold,0))                              AS available,
          IFNULL(m2m.amount,0)                                                                            AS amount,
          (
                 SELECT IFNULL(SUM(paid * IF(flight.currency_id = supplier_voucher.currency_id, 1,
                        (
                               SELECT sell_rate
                               FROM   currency_rate
                               WHERE  target_currency_id = supplier_voucher.currency_id
                               AND    base_currency_id = flight.currency_id
                               AND    CURRENT = 1
                               LIMIT  1))), 0)
                 FROM   m2m_psv_flights
                 JOIN   supplier_voucher
                 ON     supplier_voucher.id = m2m_psv_flights.supplier_voucher_id
                 WHERE  flight_id = flight.id
                 AND    supplier_voucher.canceled_at IS NULL
                 AND    supplier_voucher.deleted_at IS NULL
                 AND    supplier_voucher.type = 'project')+IFNULL(m2m.psv_paid_2,0) AS psv_paid
FROM      flight
LEFT JOIN
          (
                   SELECT   flight_id,
                            SUM(m2m_order_flights.price_gross) AS sells,
                            SUM(m2m_order_flights.price_net)   AS m2m_total_net,
                            (
                                       SELECT     COUNT(*)
                                       FROM       m2m_order_flights `of`
                                       INNER JOIN `order` o
                                       ON         o.id = `of`.order_id
                                       WHERE      `of`.flight_id = m2m_order_flights.flight_id
                                       AND        o.status_id IN(1,2,3)
                                       AND        o.deleted_at IS NULL
                                       AND        `of`.deleted_at IS NULL) AS sold,
                            (
                                   SELECT SUM(seats)
                                   FROM   reserved_seats
                                   WHERE  until >= CURRENT_DATE()
                                   AND    flight_id = m2m_order_flights.flight_id
                                   AND    deleted_at IS NULL)      AS reserved,
                            # fn_getCountAvailable(flight_id, 434) AS available,
                            (
                                       SELECT     SUM(of.price_gross)
                                       FROM       m2m_order_flights `of`
                                       INNER JOIN `order` o
                                       ON         o.id = `of`.order_id
                                       WHERE      `of`.flight_id = m2m_order_flights.flight_id
                                       AND        o.deleted_at IS NULL
                                       AND        `of`.deleted_at IS NULL) AS amount,
                            (
                                   SELECT IFNULL(SUM(seats), 0)
                                   FROM   reserved_seats
                                   WHERE  until >= CURRENT_DATE()
                                   AND    flight_id = m2m_order_flights.flight_id
                                   AND    agent_id = 434) AS agent_reserved,
                            (
                                   SELECT IFNULL(SUM(price_net), 0)
                                   FROM   m2m_order_flights `of`
                                   WHERE  m2m_order_flights.flight_id = `of`.flight_id
                                   AND    `of`.supplier_voucher_id > 0) AS psv_paid_2
                   FROM     `m2m_order_flights`
                   WHERE    m2m_order_flights.deleted_at IS NULL #WHERE fn_isAvailableFlight(flight_id, 434) = 1
                   GROUP BY flight_id) AS m2m
ON        flight.id = m2m.flight_id
WHERE     `flight`.`deleted_at` IS NULL
AND       `flight`.`project_id` = 359
AND       ((
                              ifnull(flight.seats,0)-ifnull(m2m.reserved,0)-ifnull(m2m.sold,0)>0)
          OR        (
                              ifnull(agent_reserved,0)>0))
  • 1
    What is your question? – Laurenz Albe Sep 9 at 14:08
  • 1
    I would start by formatting the query, right now it is very difficult to see what level a certain predicate belongs to – Lennart Sep 9 at 14:20
1

"explode-implode"

First it gathers all the colunns from flights, then joins to probably multiple rows in a second table. This leads to duplicating all the columns in flights. At then end you boil it down to exactly one copy.

SO...

  1. Start by doing all the necessary sums in m2m_order_flights. This should be boiled down to one row per flight_id. You should not have to touch flights for this step.
  2. Now JOIN to flights to grab its column only once.

No GROUP BY at the end. only in the subquery involving m2m_order_flights.

If that is not enough, we can look into those time-consuming procs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.