Laravel Eloquent Advanced Query Patterns for Large Datasets

By Kokil Thapa | Last reviewed: September 2026

When a Laravel application crosses a few hundred thousand rows, the queries that felt fine in staging start timing out in production. Laravel Eloquent Advanced Query Patterns for Large Datasets are not optional extras—they are the difference between a report that finishes in seconds and one that exhausts PHP memory on a shared host. On real client projects—booking systems, legal portals, eCommerce order histories—I have watched teams reach for raw SQL too early, or worse, load entire tables into collections. Eloquent is expressive enough to handle scale if you treat the database as a filter engine, not a data shuttle. This guide walks through the patterns I reach for on production Laravel 13.x apps running PHP 8.5 against MySQL 9.7 or PostgreSQL 18, with pointers to related work on advanced Eloquent techniques for complex applications and database query caching strategies.

Why Do Laravel Eloquent Queries Slow Down on Large Datasets?

Eloquent hides SQL, which is useful until it hides cost. Three problems show up repeatedly on production systems I maintain.

• Memory blow-ups: Model::all() or get() on an unbounded query loads every matching row into a Collection. PHP 8.5 raises limits, but a 2 GB FPM worker still dies.
• N+1 queries: One list query plus one query per row for relations. A 5,000-row admin table becomes 5,001 round trips.
• Offset pagination: LIMIT 20 OFFSET 500000 forces the engine to scan and discard half a million rows. Page numbers feel familiar; performance does not.

The fix is architectural discipline, not abandoning Eloquent. You push filtering, sorting, and aggregation to MySQL or PostgreSQL, stream results through the application in bounded batches, and cache only stable read models. That aligns with modern Laravel architecture best practices and the reporting workloads I have seen on platforms like Adventure Third Pole Trek, where booking and supplier data grows every season.

How Do You Stream Large Result Sets Without Running Out of Memory?

Never call get() on an export or nightly sync. Laravel gives you three streaming primitives; pick based on whether you need ORM models, stable ordering, or maximum throughput.

chunk() for batch processing

chunk() runs repeated queries with LIMIT and OFFSET internally. It is simple and works well up to low millions of rows if you keep chunk size modest (200–1000).

Order::query()
    ->where('status', 'paid')
    ->where('created_at', '>=', now()->subYear())
    ->orderBy('id')
    ->chunk(500, function ($orders) {
        foreach ($orders as $order) {
            GenerateInvoicePdf::dispatch($order->id);
        }
    });

Always include a deterministic orderBy on an indexed column—usually the primary key. Without it, rows can shift between chunks during concurrent writes and you will skip or duplicate records.

chunkById() to avoid offset cost

For tables where id is monotonic, chunkById() replaces OFFSET with WHERE id > ?, which uses the primary key index directly. This is my default on order, payment, and audit tables.

AuditLog::query()
    ->where('created_at', '<', now()->subMonths(6))
    ->chunkById(1000, function ($logs) {
        ArchiveAuditLogs::dispatch($logs->pluck('id')->all());
    }, column: 'id');

cursor() and lazy() for single-pass iteration

cursor() uses PHP generators and issues one query with a server-side cursor (MySQL) or repeated fetches depending on driver configuration. Memory stays flat because only one model hydrates at a time.

foreach (Product::query()->select(['id', 'sku', 'stock'])->cursor() as $product) {
    if ($product->stock < 5) {
        LowStockAlert::dispatch($product->id);
    }
}

lazy() (Laravel 12+) batches internally—often the best compromise when you want generator semantics without tuning fetch sizes yourself. On a legal-tech portal I built, switching a document re-index job from get() to lazy(500) dropped peak memory from 512 MB to under 40 MB on the same dataset.

How Do You Prevent N+1 Queries on Large Eloquent Result Sets?

N+1 hurts most when the parent query already returns thousands of rows. Eager loading is the first fix; constrained eager loading is the one that scales.

Constrained eager loads

Booking::query()
    ->with([
        'customer:id,name,email',
        'payments' => fn ($q) => $q
            ->select(['id', 'booking_id', 'amount', 'status'])
            ->where('status', 'captured')
            ->latest()
            ->limit(3),
    ])
    ->whereBetween('check_in', [$start, $end])
    ->paginate(50);

Notice column lists on both sides of the relation. MySQL and PostgreSQL require foreign keys in select() when you trim columns—omit booking_id and the relation silently breaks.

withCount and withExists instead of loading children

When you only need aggregates, never hydrate child models:

Vendor::query()
    ->withCount(['deals as active_deals_count' => fn ($q) => $q->where('expires_at', '>', now())])
    ->withExists('kycVerification as is_verified')
    ->orderByDesc('active_deals_count')
    ->paginate(30);

On directory platforms similar to Ajako Deal, listing pages used withCount instead of loading full deal collections—query count dropped from 120+ to 4 per request. Pair this with testing and optimization that includes query assertions in feature tests.

Detect N+1 in development

Install Laravel Debugbar in local environments only. In CI, assert query counts:

$this->assertDatabaseCount('bookings', 50);

$this->get('/admin/bookings')
    ->assertOk();

$this->assertLessThan(10, count(DB::getQueryLog()));

Which Subquery and Join Patterns Scale Better Than Naive Eloquent?

Some filters are expressible in Eloquent but execute terribly. Subqueries and strategic joins keep the optimiser on indexed paths. The official Laravel 13.x Eloquent documentation covers the builder methods; the production trick is knowing when to use them.

whereIn with a subquery instead of pluck()

A common mistake: pluck thousands of IDs into PHP, then pass them to whereIn. That bloats memory and can exceed max_allowed_packet on MySQL.

User::query()
    ->whereIn('id', function ($query) {
        $query->select('user_id')
            ->from('orders')
            ->where('paid_at', '>=', now()->subDays(30))
            ->groupBy('user_id')
            ->havingRaw('SUM(total) > ?', [50000]);
    })
    ->select(['id', 'name', 'email'])
    ->cursor();

The database correlates internally—no giant ID array crosses the wire.

whereExists for semi-join filters

Product::query()
    ->whereExists(function ($q) {
        $q->selectRaw('1')
          ->from('order_items')
          ->whereColumn('order_items.product_id', 'products.id')
          ->where('order_items.created_at', '>=', now()->subDays(7));
    })
    ->orderByDesc('updated_at')
    ->limit(100)
    ->get();

On PostgreSQL 18, EXISTS often plans as an anti-join with nested loop on indexed foreign keys—verify with EXPLAIN (ANALYZE, BUFFERS). For MySQL 9.7, compare against a plain inner join; optimiser choice varies by statistics.

Read-only replicas and heavy reports

Separate reporting connections in config/database.php so long-running analytics do not contend with checkout transactions. I use this on Nepal Gift Card-style eCommerce where ledger reports scan months of redemptions. Cross-database patterns also appear in PostgreSQL for Laravel developers and CQRS pattern in Laravel when it helps.

How Should You Index and Paginate for Million-Row Laravel Tables?

Application patterns fail if the schema fights them. Index design is part of Eloquent work, not only DBA work.

Composite indexes aligned to WHERE + ORDER BY

If your query is:

Order::where('status', 'shipped')
    ->where('warehouse_id', 3)
    ->orderByDesc('shipped_at');

Add a composite index:

Schema::table('orders', function (Blueprint $table) {
    $table->index(['warehouse_id', 'status', 'shipped_at']);
});

Column order matters: equality filters first, range/sort last. Confirm with EXPLAIN—the MySQL 9.7 EXPLAIN documentation is the reference when rows examined still explode.

Keyset (cursor) pagination for APIs

Replace offset pagination on large tables:

public function index(Request $request)
{
    $cursor = $request->integer('cursor');
    $limit = 50;

    $items = Transaction::query()
        ->when($cursor, fn ($q) => $q->where('id', '<', $cursor))
        ->orderByDesc('id')
        ->limit($limit + 1)
        ->get();

    $hasMore = $items->count() > $limit;
    $page = $items->take($limit);
    $nextCursor = $hasMore ? $page->last()->id : null;

    return TransactionResource::collection($page)
        ->additional(['meta' => ['next_cursor' => $nextCursor]]);
}

Document the contract in your API—clients pass cursor, not page. See Laravel API best practices and building RESTful APIs with Laravel for pagination metadata conventions.

Search beyond SQL

When users expect Google-like search across products or legal articles, offload to Meilisearch or Elasticsearch and treat SQL as the source of truth. The sync job itself should use chunkById. Full setup is covered in Laravel Meilisearch integration—a pattern I prefer over LIKE '%term%' on multi-million-row tables.

When Should You Cache, Denormalise, or Reach for Raw SQL?

Eloquent is not a vow of purity. On hot read paths—dashboard totals, category counts, exchange rates cached beside Nepal forex rates—store precomputed values.

1. Cache remember with tags: Wrap expensive aggregates in Cache::tags(['reports'])->remember() when using Redis 8.10. Invalidate tags on write, not TTL alone.
2. Denormalise counters: Maintain orders_count on customers via observers or queued increments. Reads become O(1).
3. Raw SQL for reports: Use DB::select() with bound parameters for monthly revenue rollups. Keep them in repository classes, not controllers.
4. Queue the work: Never run a 90-second query inside an HTTP request. Return 202 and poll, or email the CSV link.

On Mijar Law Associates, client document lists used tagged Redis caches keyed by firm and month; invalidation fired when uploads completed. That sat alongside careful indexing on (client_id, created_at). For schema-flexible audit trails where relational joins hurt, evaluate MongoDB for Laravel real use cases—but default to MySQL or PostgreSQL until you hit a proven pain point.

Validate query payloads during development with a JSON formatter when debugging API filters, and profile before shipping through speed optimization audits. Enterprise builds that mix ledger SQL with application code often benefit from enterprise application development discipline: migrations reviewed for index impact, and slow-query logging enabled in staging.

The Redis documentation covers tagged cache eviction semantics—read it before relying on tags in cluster mode. If you outsource ongoing query tuning after launch, support and maintenance retainers should include quarterly EXPLAIN reviews on the top ten slow queries from the log.

Key Takeaways

• Stream large tables with chunkById(), cursor(), or lazy()—never unbounded get() on production jobs.
• Fix N+1 with constrained with(), withCount(), and explicit select() lists on both sides of relations.
• Replace offset pagination with keyset cursors on APIs serving million-row tables.
• Push filters into subqueries and whereExists instead of plucking massive ID arrays into PHP.
• Align composite indexes to your actual WHERE and ORDER BY columns, then verify with EXPLAIN.
• Cache stable aggregates in Redis with tag invalidation; offload full-text search to Meilisearch when SQL LIKE fails.

People Also Ask

Is Eloquent too slow for millions of rows?

Eloquent overhead per row is small compared to I/O and missing indexes. Millions of rows fail because of unbounded loads, N+1 queries, and offset pagination—not because the ORM exists. Streaming APIs and selective columns keep Eloquent viable at scale.

What chunk size should I use in Laravel?

Start with 500 for balanced memory and query count. Raise toward 1,000–2,000 on narrow tables with few columns; lower to 100–200 when each row triggers heavy processing or large JSON fields. Measure peak memory on a staging clone with production-like data volume.

Should I use DB::raw instead of Eloquent on large datasets?

Use raw SQL for complex reporting queries that Eloquent makes awkward, but keep them in dedicated repository classes with bound parameters. Day-to-day filtering, relations, and streaming still belong on the query builder or Eloquent for readability and testability.

Does lazy loading ever make sense at scale?

Explicit lazy loading (loadMissing()) helps when most rows do not need a relation— for example, only 5% of orders have disputes. Blind lazy loading in loops is the N+1 anti-pattern; intentional lazy loading after a cheap existence check can reduce total queries.

Ship Queries That Survive Real Traffic

Production data always outgrows the seed file. Laravel Eloquent advanced query patterns for large datasets—chunked exports, keyset APIs, subquery filters, index-aware schemas, and selective caching—let you keep expressive ORM code without pretending PHP can hold the whole table. Start with the slowest query in your log, run EXPLAIN, and apply one pattern at a time. If you are planning a high-volume Laravel build—a marketplace, booking engine, or legal portal—and want query architecture reviewed before launch, contact us or explore web development services. Related reading: why Laravel fits Nepali businesses and the Court Marriage In Nepal portal where lean list queries matter for SEO and lead capture.