I Made My Laravel API 83% Faster by Rethinking Database Queries

Source: Dev.to

How I solved the N+1 query problem using JSON aggregation instead of traditional eager loading

Last month, I was debugging a slow admin dashboard. The page loaded 500 partner records with their profiles, countries, and promotional codes. Each page load took over 2 seconds. The culprit? The classic N+1 query problem.

The Problem Everyone Knows

Even with Laravel’s eager loading, I was still hitting the database 5 times per request:

$partners = Partner::with(['profile', 'country', 'promocodes'])->get();

This generates:

SELECT * FROM partners;                         -- Query 1
SELECT * FROM profiles WHERE partner_id IN ...; -- Query 2
SELECT * FROM countries WHERE id IN ...;          -- Query 3
SELECT * FROM promocodes WHERE partner_id IN ...;-- Query 4

Each query adds a round‑trip to the database. With 50 records, that’s 4 network round‑trips, adding 15‑20 ms of latency each.

The “Aha!” Moment

I asked myself: “Can we load everything in ONE query?”
MySQL’s JSON_OBJECT and JSON_ARRAYAGG functions made that possible: aggregate all relations into JSON directly in SQL.

The Solution: JSON Aggregation

I built a Laravel package that does exactly this:

$partners = Partner::aggregatedQuery()
    ->withJsonRelation('profile')
    ->withJsonRelation('country')
    ->withJsonCollection('promocodes')
    ->get();

It generates a single optimized query:

SELECT 
    base.*,
    JSON_OBJECT('id', profile.id, 'name', profile.name) AS profile,
    JSON_OBJECT('id', country.id, 'name', country.name) AS country,
    (SELECT JSON_ARRAYAGG(JSON_OBJECT('id', id, 'code', code))
     FROM promocodes WHERE partner_id = base.id) AS promocodes
FROM partners base
LEFT JOIN profiles  profile  ON profile.partner_id = base.id
LEFT JOIN countries country ON country.id = base.country_id;

One query. All the data.

The Results

Benchmarks on a dataset of 2,000 partners with 4 relations each (fetching 50 records):

Improvement:

• 83 % faster response time
• 91 % less memory usage
• 75 % fewer database queries

That’s not a typo. 83 % faster.

Why Is It So Fast?

1. Network Latency (≈ 80 % of the gain)

Database round‑trips are expensive. Even on localhost each query adds 5‑10 ms; on a remote DB it’s 15‑20 ms.

Before: 4 queries × 15 ms = 60 ms network time
After: 1 query × 15 ms = 15 ms network time

2. Avoiding Eloquent Hydration (≈ 15 % of the gain)

Returning plain arrays skips object instantiation, attribute casting, relationship binding, and event firing.

3. Optimized SQL (≈ 5 % of the gain)

The database performs aggregation in highly optimized C code instead of PHP loops.

Real‑World Impact

On a dashboard handling 10,000 API requests per day:

• 40,000 fewer database queries
• 4 minutes saved in total response time
• 19 GB less memory usage
• Better server resource utilization

How It Works

Installation

composer require rgalstyan/laravel-aggregated-queries

Setup

Add the trait to your model:

use Rgalstyan\LaravelAggregatedQueries\HasAggregatedQueries;

class Partner extends Model
{
    use HasAggregatedQueries;

    public function profile()
    {
        return $this->hasOne(PartnerProfile::class);
    }

    public function promocodes()
    {
        return $this->hasMany(PartnerPromocode::class);
    }
}

Usage

// Traditional eager loading
$partners = Partner::with(['profile', 'promocodes'])->get();

// Aggregated query
$partners = Partner::aggregatedQuery()
    ->withJsonRelation('profile', ['id', 'name', 'email'])
    ->withJsonCollection('promocodes', ['id', 'code', 'discount'])
    ->where('is_active', true)
    ->get();

Output

The returned structure is predictable:

[
    'id'      => 1,
    'name'    => 'Partner A',
    'profile' => [
        'id'    => 10,
        'name'  => 'John',
        'email' => 'john@example.com',
    ],
    'promocodes' => [
        ['id' => 1, 'code' => 'SAVE10'],
        ['id' => 2, 'code' => 'SAVE20'],
    ],
];

• Relations are always an array or null.
• Collections are always an array (never null).

When Should You Use This?

✅ Perfect for

• API endpoints with multiple relations
• Admin dashboards with complex queries
• Mobile backends where every millisecond counts
• Read‑heavy applications (90 %+ reads)
• High‑traffic services needing optimization

⚠️ Not recommended for

• Write operations (use standard Eloquent)
• Scenarios requiring model events/observers
• Deeply nested relations (supported in v1.1)

Performance vs. Eloquent Models

The package offers two modes:

// Array mode (default, fastest – 83 % faster)
$partners = Partner::aggregatedQuery()->get();

// Eloquent mode (still faster – ~27 % improvement)
$partners = Partner::aggregatedQuery()->get('eloquent');

Array mode skips Eloquent’s hydration overhead. Even in Eloquent mode you still save one database query, delivering a noticeable boost.

Trade‑offs

What you lose

• Model events (created, updated, deleted)
• Accessors / mutators in array mode
• Ability to call save() or update() (read‑only)

What you gain

• 83 % faster response times
• 91 % less memory usage
• Simpler, more predictable data structures
• Better scalability for read‑heavy workloads

What’s Next?

v1.1.0 is in development with:

• Nested relations (profile.company.country)
• Conditional loading with query constraints
• Relation aliasing
• Enhanced debugging tools

Try It Out!

If you’re building APIs or dashboards with Laravel, give the package a spin. Feedback is welcome—share your results or alternative N+1 solutions in the comments.

P.S. The package has been featured in Laravel News! If you find it useful, a GitHub star would mean a lot ⭐