SQL Queries: Stop Killing Your Database

Your database is at 90% CPU. Queries taking 10 seconds. Users complaining.

Here's how to fix it.

The N+1 Problem

Most common performance killer:

// Get all orders
$orders = Order::all();

// Get customer for each order
foreach ($orders as $order) {
    echo $order->customer->name;  // Query for EACH order
}

100 orders = 101 queries (1 for orders + 100 for customers).

Fix: Eager Loading

$orders = Order::with('customer')->get();

foreach ($orders as $order) {
    echo $order->customer->name;  // No extra queries
}

100 orders = 2 queries (1 for orders + 1 for all customers).

Real impact: Dashboard load time 8s → 0.4s.

SELECT Only What You Need

// Bad: Gets all columns
$users = User::all();

// Good: Gets only needed columns
$users = User::select('id', 'name', 'email')->get();

Why it matters:

Table with 20 columns, 100,000 rows:

• SELECT *: 50MB transferred
• SELECT id, name: 5MB transferred

10x less data = 10x faster.

Avoid SELECT * in Joins

// Bad: Gets all columns from both tables
$orders = DB::table('orders')
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->select('*')
    ->get();

// Good: Specify columns
$orders = DB::table('orders')
    ->join('users', 'orders.user_id', '=', 'users.id')
    ->select('orders.*', 'users.name', 'users.email')
    ->get();

Use WHERE Before JOIN

// Bad: Joins all, then filters
SELECT orders.*, users.name
FROM orders
JOIN users ON orders.user_id = users.id
WHERE orders.status = 'pending';

// Good: Filters first, then joins
SELECT orders.*, users.name
FROM orders
WHERE orders.status = 'pending'
JOIN users ON orders.user_id = users.id;

Database processes fewer rows.

Limit Results

// Bad: Gets everything
$products = Product::where('category', 'electronics')->get();

// Good: Limits results
$products = Product::where('category', 'electronics')
    ->limit(20)
    ->get();

Need pagination? Even better:

$products = Product::where('category', 'electronics')
    ->paginate(20);

Avoid OR in WHERE

// Slow: Can't use indexes efficiently
SELECT * FROM products
WHERE category = 'electronics'
OR category = 'computers'
OR category = 'phones';

// Fast: Uses index
SELECT * FROM products
WHERE category IN ('electronics', 'computers', 'phones');

Laravel:

// Slow
Product::where('category', 'electronics')
    ->orWhere('category', 'computers')
    ->orWhere('category', 'phones')
    ->get();

// Fast
Product::whereIn('category', ['electronics', 'computers', 'phones'])
    ->get();

Use EXISTS Instead of COUNT

// Slow: Counts all matching rows
if (Order::where('user_id', $userId)->count() > 0) {
    // User has orders
}

// Fast: Stops at first match
if (Order::where('user_id', $userId)->exists()) {
    // User has orders
}

Chunk Large Datasets

// Bad: Loads 1 million rows into memory
$users = User::all();
foreach ($users as $user) {
    $user->sendEmail();
}

// Good: Processes in chunks
User::chunk(1000, function ($users) {
    foreach ($users as $user) {
        $user->sendEmail();
    }
});

Real Project Example

Client: E-commerce site, product listing page taking 12 seconds.

Original query:

$products = Product::with('category', 'images', 'reviews')
    ->where('status', 'active')
    ->get();

Problems:

• Loading all products (50,000+)
• Loading all images (200,000+)
• Loading all reviews (500,000+)
• No pagination

Optimized query:

$products = Product::select('id', 'name', 'price', 'category_id')
    ->with([
        'category:id,name',
        'images' => fn($q) => $q->select('id', 'product_id', 'url')->limit(1),
    ])
    ->withCount('reviews')
    ->withAvg('reviews', 'rating')
    ->where('status', 'active')
    ->paginate(20);

Results:

• Load time: 12s → 0.3s
• Memory usage: 500MB → 15MB
• Database CPU: 85% → 20%

Query Analysis

Use EXPLAIN

EXPLAIN SELECT * FROM orders WHERE user_id = 123;

Look for:

• type: ALL = Full table scan (bad)
• type: ref = Using index (good)
• rows: 1000000 = Scanning too many rows

Laravel Query Log

DB::enableQueryLog();

// Your code here

dd(DB::getQueryLog());

Shows all queries executed. Find slow ones.

Slow Query Log

-- MySQL
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

-- Check slow queries
SELECT * FROM mysql.slow_log;

Common Mistakes

Mistake 1: Using LIKE with Leading Wildcard

// Slow: Can't use index
Product::where('name', 'LIKE', '%phone%')->get();

// Fast: Can use index
Product::where('name', 'LIKE', 'phone%')->get();

For full-text search, use full-text index:

Product::whereRaw('MATCH(name) AGAINST(?)', ['phone'])->get();

Mistake 2: Functions in WHERE

// Slow: Can't use index
SELECT * FROM orders WHERE DATE(created_at) = '2026-03-01';

// Fast: Uses index
SELECT * FROM orders 
WHERE created_at >= '2026-03-01 00:00:00' 
AND created_at < '2026-03-02 00:00:00';

Mistake 3: Not Using Indexes

// Add index for frequently queried columns
Schema::table('orders', function (Blueprint $table) {
    $table->index('user_id');
    $table->index('status');
    $table->index(['user_id', 'status']);
});

Optimization Checklist

• Use eager loading (avoid N+1)
• SELECT only needed columns
• Add indexes for WHERE/JOIN columns
• Use pagination
• Avoid SELECT * in joins
• Use EXISTS instead of COUNT
• Chunk large datasets
• Cache expensive queries
• Monitor slow queries
• Use EXPLAIN to analyze

Bottom Line

Most performance issues are bad queries.

Eager load relationships. Select only what you need. Add indexes. Paginate.

These simple changes can make queries 100x faster.

---

Need database optimization?

We fix slow databases for Nigerian businesses. Query optimization, indexing, performance tuning.

📞 WhatsApp: +234 708 711 0468
📧 info@raspibtech.com
📍 Lagos Island

Related:

• Database Indexing
• Redis Caching