Eliminating N+1 Queries & Indexing Multi-Tenant Tables: Optimizing Slow Database Queries under Heavy SaaS Load
How we diagnosed slow database queries, implemented composite indexes on multi-tenant tables, and eliminated hidden N+1 query bottlenecks in production.
Eliminating N+1 Queries & Indexing Multi-Tenant Tables: Optimizing Slow Database Queries under Heavy SaaS Load
Laravel Multi-Tenancy Masterclass
Multi-Tenant Query & Database Indexing Optimization
As multi-tenant SaaS databases grow into millions of records, unindexed queries and hidden N+1 query bottlenecks degrade application performance.
When fetching tenant records like invoices, customer movements, or stock items, filtering by company_id without composite index strategies causes full table scans. Here is how we optimized multi-tenant database queries and query execution times under heavy SaaS load.
1. Designing Composite Database Indexes for Multi-Tenant Models
In multi-tenant schemas, queries almost always filter by company_id alongside filtering parameters like status or created_at. Single-column indexes are inefficient for multi-clause WHERE statements.
class=class="text-emerald-500 dark:text-emerald-400 font-medium">"text-slate-500 italic">// database/migrations/2026_03_30_000000_add_composite_indexes_to_invoices_table.php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
public function up(): void
{
Schema::table('invoices', function (Blueprint $table) {
class=class="text-emerald-500 dark:text-emerald-400 font-medium">"text-slate-500 italic">// Composite index ordered by (company_id, status, created_at)
$table->index(['company_id', 'status', 'created_at'], 'idx_invoices_tenant_status_date');
class=class="text-emerald-500 dark:text-emerald-400 font-medium">"text-slate-500 italic">// Composite index for customer invoice searches
$table->index(['company_id', 'customer_id'], 'idx_invoices_tenant_customer');
});
}
public function down(): void
{
Schema::table('invoices', function (Blueprint $table) {
$table->dropIndex('idx_invoices_tenant_status_date');
$table->dropIndex('idx_invoices_tenant_customer');
});
}
};2. Detecting & Eliminating N+1 Query Patterns
N+1 queries occur when a list of parent models iterates over child relationships without eager loading.
Problematic N+1 Code:
class=class="text-emerald-500 dark:text-emerald-400 font-medium">"text-slate-500 italic">// Executes 1 query for invoices + N queries for customer details!
$invoices = Invoice::where('company_id', $companyId)->get();
foreach ($invoices as $invoice) {
echo $invoice->customer->name; class=class="text-emerald-500 dark:text-emerald-400 font-medium">"text-slate-500 italic">// Triggering separate SQL queries per row!
}Optimized Eager-Loaded Code:
class=class="text-emerald-500 dark:text-emerald-400 font-medium">"text-slate-500 italic">// Executes exactly 2 optimized SQL queries regardless of invoice count!
$invoices = Invoice::where('company_id', $companyId)
->with(['customer:id,name,email', 'items:id,invoice_id,quantity,unit_price'])
->select(['id', 'company_id', 'customer_id', 'total_amount', 'status', 'created_at'])
->paginate(25);3. Profiling Execution Plans with EXPLAIN Queries
Using MySQL EXPLAIN analysis, verify that your composite indexes eliminate full table scans:
EXPLAIN SELECT id, total_amount, status
FROM invoices
WHERE company_id = 42 AND status = 'PAID'
ORDER BY created_at DESC;- Key used:
idx_invoices_tenant_status_date - Rows scanned: Reduced from 450,000+ rows down to 12 rows.
- Query Execution Time: Dropped from 1,450ms to 4ms!
Implementing composite multi-tenant indexes and eager loading relationships eliminated database latency spikes and ensured smooth sub-50ms page load times across the application.