PHP Academy · Lesson

Optimizing Database Queries

Identify N+1 queries, add indexes, and reduce query count.

Optimizing Database Queries is a free PHP Academy lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PHP Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Identify Slow Queries

Enable the MySQL slow query log and set a threshold. Review queries taking longer than expected.

# my.cnf:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5  # log queries over 500ms

EXPLAIN / EXPLAIN ANALYZE

Use EXPLAIN to understand how MySQL executes a query and whether indexes are used.

-- Run in MySQL:
EXPLAIN SELECT * FROM orders WHERE user_id = 42 AND status = "pending";
-- Look for: type=ALL (full scan), key=NULL (no index used)

Adding Indexes

Indexes dramatically speed up reads on filtered and sorted columns.

-- Add composite index for the query above:
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

// In Laravel migration:
$table->index(["user_id", "status"]);

N+1 Query Problem

The N+1 problem fires one query to get a list plus one query per item for a relationship. Fix with eager loading.

<?php
// BAD: 1 + N queries
$posts = Post::all();
foreach ($posts as $post) echo $post->author->name;

// GOOD: 2 queries
$posts = Post::with("author")->get();
foreach ($posts as $post) echo $post->author->name;

Select Only Needed Columns

Avoid SELECT * — fetch only the columns you need to reduce memory usage and network transfer.

<?php
// Bad:
$users = User::all();

// Good:
$users = User::select("id", "name", "email")->get();

Chunking Large Datasets

Process large result sets in chunks to avoid memory exhaustion.

<?php
User::chunk(500, function ($users) {
    foreach ($users as $user) {
        // process each user
    }
});

// Or lazy loading (PHP generator):
User::lazy()->each(fn($user) => processUser($user));

Query Caching

Cache expensive aggregations and reports that do not change frequently.

<?php
$revenue = Cache::remember("monthly-revenue", 3600, function () {
    return Order::whereMonth("created_at", now()->month)->sum("total");
});

Avoid Queries in Loops

Never run queries inside foreach loops. Collect IDs first, then fetch in one query.

<?php
// Collect all product IDs:
$ids = array_column($cartItems, "product_id");
// One query:
$products = Product::whereIn("id", $ids)->get()->keyBy("id");

foreach ($cartItems as $item) {
    $product = $products[$item["product_id"]];
}

Database Connection Pooling

PHP-FPM creates a new PDO connection per worker. PgBouncer or ProxySQL can pool connections to prevent overwhelming the DB with connection overhead.

Covering Indexes

A covering index includes all columns needed to satisfy a query — MySQL can answer the query from the index alone without touching the main table rows.

Summary

Enable slow query logs. Use EXPLAIN to find missing indexes. Eager-load relationships. Select only needed columns. Chunk large datasets. Cache expensive aggregations. Avoid queries inside loops.

Quick Check

What is the N+1 query problem?

Frequently asked questions

Is the “Optimizing Database Queries” lesson free?

Yes — the full text of “Optimizing Database Queries” is free to read here on the web, and the PHP Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PHP Academy course, upgrade to CoddyKit PRO.

What will I learn in “Optimizing Database Queries”?

Identify N+1 queries, add indexes, and reduce query count. You practise PHP Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PHP Academy?

No prior experience is required. PHP Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Optimizing Database Queries” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PHP Academy lesson?

Yes. Every PHP Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to PHP Academy