Laravel Query Optimization for 1M+ Row Tables

By Kokil Thapa | Last reviewed: August 2026

Laravel query optimization for 1M+ row tables stops being optional the moment your admin panel takes eight seconds to load or your API timeout errors start spiking. At this scale, the ORM convenience that makes hiring a Laravel developer attractive can mask expensive full-table scans and memory exhaustion if you rely solely on default Eloquent patterns. The fix is rarely rewriting your entire application; it is usually a combination of targeted composite indexes, disciplined eager loading, and moving heavy aggregation off the PHP layer.

How Do You Diagnose Slow Laravel Queries on Large Tables?

Before changing code, you must confirm what the database engine is actually doing. Guessing leads to adding useless indexes that slow down writes without fixing reads. In my experience working on production Laravel applications with multi-million row datasets, the diagnosis phase saves more time than any premature optimization.

Enable Query Logging and EXPLAIN Analysis

Laravel’s debugbar or telescope is useful for spotting query counts, but for large tables you need the raw SQL and its execution plan. Enable the query log temporarily in a development or staging environment connected to a representative dataset:

<?php // In a controller or command for debugging only DB::enableQueryLog(); $orders = Order::where('status', 'pending') ->whereDate('created_at', '>=', '2026-01-01') ->orderByDesc('created_at') ->limit(50) ->get(); $queries = DB::getQueryLog(); foreach ($queries as $query) { Log::debug('SQL: ' . $query['query']); Log::debug('Bindings: ', $query['bindings']); }

Copy the logged SQL and run EXPLAIN ANALYZE (MySQL 8.0+) or EXPLAIN (ANALYZE, BUFFERS) (PostgreSQL 16+) directly against the database. Look for these red flags:

• type: ALL — Full table scan on 1M+ rows is almost always wrong for user-facing queries.
• rows examined >> rows returned — The engine is reading thousands of rows to find dozens.
• Using filesort — Sorting happens in memory/disk after retrieval because no index covers the ORDER BY.
• Using temporary — GROUP BY or DISTINCT cannot use an index and creates a temp table.

Profile Hydration Cost Separately from Query Time

A common mistake is assuming a slow endpoint means a slow SQL query. On a legal-tech portal I built with extensive case records, the SQL executed in 40ms but the endpoint took 2.3 seconds because Eloquent was hydrating 5,000 model instances with relationships. Use toSql() and benchmark the raw DB call versus the Eloquent call. If the gap is large, your bottleneck is PHP object creation, not the database. This distinction determines whether you should optimize the index or switch to cursor(), chunking, or raw selects.

Which Indexing Strategies Actually Work for Million-Row Eloquent Queries?

Indexes are the single highest-impact lever for Laravel query optimization for 1M+ row tables, but only if they match your actual access patterns. A generic index on every foreign key is insufficient for complex filtering.

Build Composite Indexes Matching Your Where Clause Order

MySQL and PostgreSQL use B-tree indexes left-to-right. An index on (status, created_at) helps WHERE status = ? ORDER BY created_at but does NOT help WHERE created_at > ? alone. Examine your most frequent slow queries and build indexes that cover the equality conditions first, then range/sort columns.

// Migration example for an orders table with 2M+ rows Schema::table('orders', function (Blueprint $table) { // Covers: WHERE user_id = ? AND status = ? ORDER BY created_at DESC $table->index(['user_id', 'status', 'created_at'], 'idx_user_status_created'); // Covers: Admin listing filtered by status + date range $table->index(['status', 'created_at'], 'idx_status_created'); });

Avoid Over-Indexing Write-Heavy Tables

Every index slows INSERT and UPDATE operations. On high-throughput eCommerce order tables, I have seen write latency double after adding three unnecessary composite indexes. Audit indexes quarterly using sys.schema_unused_indexes in MySQL or pg_stat_user_indexes in PostgreSQL. Drop anything unused for 90 days unless it serves a known seasonal report.

Consider Covering Indexes for Read-Only Endpoints

If an API endpoint only needs id, title, and published_at from a posts table, a covering index on (status, published_at, id, title) lets MySQL satisfy the query entirely from the index without touching the main table heap. This eliminates random I/O and can reduce response time by 70–90% on million-row tables. The trade-off is index size; measure before deploying.

How Do You Eliminate N+1 Problems Without Loading Too Much Data?

N+1 queries are the most frequent cause of catastrophic performance in Laravel applications at scale, but the naive fix of eager loading everything creates its own memory crisis on large datasets.

Constrain Eager Loads Explicitly

Never write with('comments') on a listing page showing 100 posts when each post has 500 comments. You just loaded 50,000 comment models into memory. Always constrain:

$posts = Post::with(['comments' => function ($query) { $query->select('id', 'post_id', 'body', 'user_id') ->where('is_approved', true) ->latest() ->limit(10); }, 'author:id,name']) ->select('id', 'title', 'slug', 'author_id', 'published_at') ->where('published_at', '<=', now()) ->paginate(20);

This pattern ensures you load exactly what the view needs. On a directory site with vendor listings and reviews, this reduced peak memory from 380MB to 45MB per request.

Use Lazy Eager Loading for Conditional Relations

Sometimes you cannot determine which relations to load upfront. Instead of eagerly loading everything "just in case," use loadMissing() inside loops or resource classes. This prevents duplicate loads while avoiding upfront over-fetching:

// In an API Resource or Blade component public function toArray($request): array { $this->resource->loadMissing(['tags:id,name']); return [ 'id' => $this->id, 'title' => $this->title, 'tags' => TagResource::collection($this->tags), ]; }

Detect N+1 Automatically in Development

Add the beyondcode/laravel-query-detector package to your dev dependencies. It alerts you during local development whenever an N+1 pattern occurs. Combine this with Laravel Debugbar’s query timeline to catch regressions before they reach production. Never rely on manual code review alone for a codebase with hundreds of endpoints.

When Should You Bypass Eloquent for Aggregations and Reports?

Eloquent excels at CRUD operations on individual entities. It fails at analytics, dashboards, and exports on million-row tables because it hydrates every matched row into a PHP object. For these workloads, bypass the ORM deliberately.

Push Aggregations to the Database Engine

Counting, summing, averaging, and grouping should never happen in PHP when the dataset exceeds 10,000 rows. The database engine has optimized C++ routines for this; PHP does not.

// WRONG for 1M+ rows $totalRevenue = Order::whereYear('created_at', 2026) ->get() ->sum('total_amount'); // RIGHT — single value returned from DB $totalRevenue = Order::whereYear('created_at', 2026) ->sum('total_amount'); // Complex grouping without hydration $monthlyStats = DB::table('orders') ->selectRaw("DATE_FORMAT(created_at, '%Y-%m') as month, SUM(total_amount) as revenue, COUNT(*) as orders") ->where('created_at', '>=', '2026-01-01') ->groupByRaw("DATE_FORMAT(created_at, '%Y-%m')") ->orderBy('month') ->get();

Use Cursor or Chunk for Bulk Processing

When you must process every row (data migration, export, batch update), never use all() or get(). Use cursor() for read-only streaming or chunkById() for safe batch updates:

// Memory-safe processing of 2M records Order::where('status', 'completed') ->chunkById(500, function ($orders) { foreach ($orders as $order) { // Process each order SyncToAccountingJob::dispatch($order); } }, column: 'id');

Note the use of chunkById instead of chunk. The latter uses OFFSET which degrades to O(n²) on large tables because MySQL must skip progressively larger result sets. chunkById uses a WHERE clause on the primary key, maintaining constant performance regardless of position.

Leverage Database Views for Complex Recurring Reports

If a dashboard query joins four tables and aggregates across millions of rows, encapsulate it in a database view. Laravel can query views like any table, and you can add indexes to materialized views in PostgreSQL or indexed views in MySQL Enterprise. This moves complexity out of PHP and lets the database optimizer cache execution plans. I have used this pattern successfully for financial reporting dashboards where the same aggregation ran on every page load.

How Do You Maintain Performance as Data Grows Beyond Initial Optimization?

Optimization is not a one-time task. Tables that perform well at 1M rows may degrade at 10M without architectural changes. Build monitoring and maintenance into your deployment workflow.

Implement Slow Query Monitoring in Production

Configure MySQL’s long_query_time to 1 second (or lower for APIs) and enable the slow query log. Parse it weekly with pt-query-digest from Percona Toolkit to identify new bottlenecks. Alternatively, use Laravel Pulse (available in Laravel 12.x) to visualize slow queries directly in your application dashboard. Set up alerts for queries exceeding thresholds rather than waiting for user complaints.

Plan Partitioning Before You Need It

For tables that grow predictably (logs, transactions, events), implement range partitioning by date before hitting 5M+ rows. Partitioning allows MySQL to prune irrelevant partitions during queries, effectively reducing the scanned dataset. Adding partitioning retroactively to a live 20M row table is risky and requires downtime; planning it during initial schema design avoids future pain.

-- Example: Monthly partitioning for an audit_logs table ALTER TABLE audit_logs PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) ( PARTITION p202601 VALUES LESS THAN (202602), PARTITION p202602 VALUES LESS THAN (202603), PARTITION p202603 VALUES LESS THAN (202604), PARTITION pmax VALUES LESS THAN MAXVALUE );

Archive Cold Data Aggressively

Most applications have a long tail of historical data that is rarely queried but inflates table size and index depth. Move records older than your active query window to an archive table or separate database. Keep the hot table lean. On an eCommerce project, archiving orders older than two years reduced the active orders table from 4.2M to 800K rows, cutting average query time by 60% without any code changes.

Review Execution Plans After Major Deployments

Data distribution changes over time. An index that was optimal when 80% of orders were "pending" may become inefficient when that drops to 5%. Schedule quarterly reviews of your top 20 queries’ execution plans. Update statistics with ANALYZE TABLE after bulk imports. Treat query performance as a living metric, not a solved problem.

Actionable Next Steps for Laravel Query Optimization

Laravel query optimization for 1M+ row tables is achievable without abandoning the framework or rewriting your application. Start today by enabling query logging on your slowest endpoint, running EXPLAIN ANALYZE, and adding one targeted composite index. Then audit your eager loads for missing constraints. Finally, move your heaviest aggregations to the database layer. These three steps alone resolve the majority of large-table performance issues I encounter in production systems.

If your team needs hands-on support diagnosing and fixing large-table performance bottlenecks in Laravel, reach out to discuss your specific situation. I regularly help teams optimize databases for legal-tech platforms, eCommerce systems, and SaaS applications handling millions of records. For related infrastructure concerns, see our guide on MySQL optimization for SaaS applications or scaling Laravel queues for high-traffic workloads.