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

How I solved the N+1 query problem using JSON aggregation instead of traditional eager loading
tags: laravel, php, performance, database.

---

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 means another round-trip to the database. With 50 records, that's 4 network round-trips, adding 15-20ms of latency each.

The "Aha!" Moment

I asked myself: "Can we load everything in ONE query?"

That's when I remembered MySQL's JSON_OBJECT and JSON_ARRAYAGG functions. What if instead of multiple queries, we could 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();

This 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

I ran benchmarks on a dataset of 2,000 partners with 4 relations each, fetching 50 records:

Method	Time	Memory	Queries
Standard Eloquent	27.44ms	2.06MB	4
JSON Aggregation	4.41ms	0.18MB	1
Improvement	83% faster	91% less	75% fewer

That's not a typo. 83% faster.

Why Is It So Fast?

The performance gain comes from three factors:

1. Network Latency (80% of the gain)

Database round-trips are expensive. Even on localhost, each query adds 5-10ms. On a remote database? 15-20ms each.

Before: 4 queries × 15ms = 60ms in network time

After: 1 query × 15ms = 15ms

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

By returning arrays instead of Eloquent models, we skip:

• Object instantiation
• Attribute casting
• Relationship binding
• Event firing

3. Optimized SQL (5% of the gain)

The database does the aggregation work using highly optimized C code instead of PHP loops.

Real-World Impact

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

• 40,000 fewer database queries
• 4 minutes saved in total response time
• 19GB 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

// Instead of this:
$partners = Partner::with(['profile', 'promocodes'])->get();

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

Output

The data structure is predictable and clean:

[
    'id' => 1,
    'name' => 'Partner A',
    'profile' => [
        'id' => 10,
        'name' => 'John',
        'email' => '[e-mail]'
    ],
    'promocodes' => [
        ['id' => 1, 'code' => 'SAVE10'],
        ['id' => 2, 'code' => 'SAVE20']
    ]
]

Guaranteed:

• Relations are array or null (never undefined)
• Collections are always array (never null)
• No surprises in your code

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)
• When you need model events/observers
• Complex nested relations (coming 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 is fastest because it skips Eloquent's hydration overhead. But even in Eloquent mode, you still save 1 database query, which gives a significant boost.

Trade-offs

Let's be honest about limitations:

What you lose:

• Model events (no created, updated, deleted events)
• Accessors/mutators (in array mode)
• The 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 operations like APIs and dashboards, this trade-off is absolutely worth it.

What's Next?

I'm currently working on v1.1.0 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 it a try:

🔗 GitHub: rgalstyan/laravel-aggregated-queries

📦 Packagist: composer require rgalstyan/laravel-aggregated-queries

I'd love to hear your results! Have you tried optimizing N+1 queries in other ways? Drop a comment below.

---

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