Changes between Version 2 and Version 3 of ApplicationDevelopment

Timestamp:

09/29/25 14:24:16 (4 weeks ago)

Author:

221507

Comment:

--

ApplicationDevelopment

@@ -10 +10 @@
  - Docker (recommended, especially for the DB)
  - Native (no containers)
+
+
+----
      
+== Indexes
+
+To optimize query performance and reduce full table scans as data volume grows, the following indexes were added based on real usage patterns in analytical queries, views, and joins.
+
+
+1. Most queries join trip and route using route_id, for example in company_performance_view, top_selling_routes_view, ticket statistics, and weighted usage reports. Without this index, every join would trigger a sequential scan of the trip table. With the index, the database can directly locate all trips for a given route. 
+
+{{{
+CREATE INDEX idx_trip_route_id ON trip(route_id);
+}}}
+
+
+----
+
+
+
+
+2. These two indexes accelerate lookups and joins involving route endpoints. They support queries that find trips between specific locations, which for our application is especially iseful to detect **subroutes**, one of the most important features. They're also useful in displaying routes with origin - destination metadata. 
+
+
+{{{
+CREATE INDEX idx_route_from_location_id ON route(from_location_id);
+CREATE INDEX idx_route_to_location_id ON route(to_location_id);
+}}}
+
+
+----
+
+
+3. Filtering by trip status (e.g., 'COMPLETED', 'NOT_STARTED') is common in **ticket statistics** and reporting queries. This index ensures that the database can filter relevant trips quickly instead of scanning the full trip table. It's especially important in analytical use cases and dashboards.
+
+
+{{{
+CREATE INDEX idx_trip_status ON trip(status);
+}}}
+
+
+----
+
+
+4. Views such as company_performance_view and top_selling_routes_view frequently join route and transport_organizer through transport_organizer_id. This index speeds up company-based route lookups, revenue calculations and per-organizer reporting. So, this index is useful for when we're calculating company metrics/revenue by organizer.
+
+
+{{{
+CREATE INDEX idx_route_transport_organizer_id ON route(transport_organizer_id);
+}}}
+
+
+
+