Changes between Version 15 and Version 16 of Profiling

Timestamp:

02/10/26 20:22:13 (3 weeks ago)

Author:

222039

Comment:

--

Profiling

@@ -114 +114 @@
 * Се намалува I/O и број на обработени редови.
 
+=== Оптимизација 2 (Уште пооптимизирано)
 
+Прво издвојуваме утрешни flight_id со  CTE, па агрегираме само за нив.
+
+{{{
+WITH tomorrow_flights AS (
+  SELECT flight_id, airline_id, airplane_id, flightno, `from`, `to`, departure, arrival
+  FROM flight
+  WHERE departure >= CURDATE()
+    AND departure < DATE_ADD(CURDATE(), INTERVAL 1 DAY)
+),
+booking_x AS (
+  SELECT b.flight_id, COUNT(*) AS booked_seats, AVG(b.price) AS avg_price
+  FROM booking b
+  JOIN tomorrow_flights tf ON tf.flight_id = b.flight_id
+  GROUP BY b.flight_id
+)
+SELECT
+  tf.flight_id,
+  al.airlinename,
+  tf.flightno,
+  tf.`from`,
+  tf.`to`,
+  tf.departure,
+  tf.arrival,
+  a.capacity,
+  bx.booked_seats,
+  bx.avg_price,
+  bx.booked_seats / a.capacity AS load_factor
+FROM tomorrow_flights tf
+JOIN booking_x bx ON bx.flight_id = tf.flight_id
+JOIN airline al ON al.airline_id = tf.airline_id
+JOIN airplane a ON a.airplane_id = tf.airplane_id
+ORDER BY load_factor DESC
+LIMIT 100;
+}}}