Optimizing Database Queries in High-Traffic Laravel Apps

Most performance problems we see in Laravel applications are not caused by Laravel. They are caused by the database being asked the wrong questions, or the right questions too many times. In a travel platform the symptoms are familiar: a reservations list that takes several seconds to load for a large agency, an invoice export that times out, or an admin page that is fast in testing and slow in production once real data arrives.

This article covers the techniques our team uses most often to find and fix these problems in Vee Operations and Vee Money.

Find the problem before fixing it

Guessing is expensive. Before changing any query, we measure. In development we run Laravel Debugbar or Telescope to see every query a page executes. In production we rely on slow query logs from PostgreSQL or MySQL and on application performance monitoring that groups queries by endpoint.

We also turn on lazy loading protection in non-production environments. With this enabled, any accidental lazy load throws an exception during development and testing instead of quietly adding queries in production.

// AppServiceProvider::boot()
Model::preventLazyLoading(! app()->isProduction());

Eliminate N+1 queries with eager loading

The classic N+1 problem looks harmless in a Blade view or Livewire component. You load 50 reservations, then for each one you display the agency name, the lead passenger and the supplier. That is one query for the list and 150 more for the related records.

Eager loading fixes this by fetching related records in a handful of queries.

$reservations = Reservation::query()
    ->with(['agency:id,name', 'leadPassenger', 'supplier:id,name'])
    ->latest('booked_at')
    ->paginate(50);

A few habits make eager loading more effective:

• Select only the columns you need on related models, especially wide tables such as suppliers or hotels.

• Use withCount and withSum for totals, such as the number of passengers or the sum of payments, instead of loading every child row.

• Constrain eager loads when you only need some of the related records, for example only confirmed segments.

Let the database do the aggregation

A common pattern in finance screens is loading every booking for a period into PHP and summing prices in a loop. This works for a small agency and fails for a large one. Aggregating in SQL is faster and uses far less memory.

$totals = Booking::query()
    ->where('agency_id', $agencyId)
    ->whereBetween('booked_at', [$from, $to])
    ->selectRaw('currency, sum(net_amount) as net, sum(selling_amount) as selling')
    ->groupBy('currency')
    ->get();

Note the grouping by currency. In a multi-currency platform, adding amounts across currencies without conversion gives a number that means nothing. Group first, then convert using the exchange rate that applied to each booking.

For large exports, use chunkById or lazyById so the application processes records in batches rather than loading hundreds of thousands of rows at once.

Index and cache for the queries you run

Indexes should follow real query patterns, not guesses. Reservation lists are usually filtered by agency and sorted by date, so a composite index on agency_id and booked_at serves them well. Supplier confirmation queues filter by status and supplier. Look at the slow query log, run EXPLAIN on the worst offenders, and add indexes that match the filter and sort columns in order.

• Put equality filters first in a composite index, then range or sort columns.

• Remove indexes nobody uses. Every index slows down writes, and booking tables are written to constantly.

• Watch out for functions on indexed columns in where clauses, such as wrapping a date column in a function, which often prevents the index from being used.

Caching is useful for data that is read often and changes rarely: airport lists, currency settings, agency pricing rules, feature switches. We cache these in Redis with clear invalidation when an admin changes them.

We are much more careful with anything related to availability or price. A cached hotel rate that is ten minutes old can be wrong. For those, we prefer short lifetimes and always re-check at the booking step. Query caching should make the platform faster without making it less accurate.

Make performance part of the routine

None of these techniques are new, but they only work if they are applied consistently. We review query counts in code review for any page that lists records, keep lazy loading protection on in development, and check slow query logs after each release. Small habits like these keep a growing travel platform fast as data volumes increase.