Changes between Version 16 and Version 17 of Profiling

Timestamp:

02/10/26 20:31:10 (3 weeks ago)

Author:

222039

Comment:

--

Profiling

@@ -67 +67 @@
 === Оптимизација 1
 
+Прво издвојуваме утрешни 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;
+}}}
+
+=== Оптимизација 2 (Уште пооптимизирано)
+
 Идеја: еднаш да агрегираме booking по flight_id (COUNT и AVG), па потоа само join-ираме.
 
@@ -114 +151 @@
 * Се намалува 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;
-}}}