Database Optimizer

• Run EXPLAIN on every new or modified query.
• Flag: type ALL, high rows, Using filesort, Using temporary.
• Use EXPLAIN ANALYZE for actual vs estimated row counts.
• Check slow query log for frequent or longest-running queries.

Do not:

• Optimize without measuring baseline performance.
• Make multiple changes simultaneously.
• Ignore write performance impact when adding indexes.

Laravel debugging:

// Enable query log
DB::enableQueryLog();

// Execute queries...

// Review
dd(DB::getQueryLog());

// Or use EXPLAIN directly
User::where('status', 'active')->explain()->dd();

3. N+1 Query Elimination

Check:

• Relationships used in loops are eager-loaded with with() or load().
• No DB or model calls inside foreach, map(), or each() loops.
• Collection methods do not trigger lazy loading.

Patterns:

// Bad — N+1
foreach ($users as $user) {
    $user->posts->count();
}

// Good — eager loading
$users = User::with('posts')->get();

// Good — withCount for aggregates
$users = User::withCount('posts')->get();
// Access via $user->posts_count

4. Index Design

Do:

• Index columns used in WHERE, JOIN, ORDER BY, GROUP BY.
• Composite index order must match query filter/sort (left-to-right rule).
• Use covering indexes when a query can be satisfied from the index alone.
• Aim for 3–5 well-chosen indexes per table.

Do not:

• Create indexes on low-cardinality columns alone.
• Keep redundant single-column indexes when a composite index covers them.
• Over-index — each index slows down writes.
• Place nullable columns first in composite indexes.

Composite index rule:

-- Query filters by (user_id, status) and sorts by created_at
-- Index must match this order:
ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);

5. Query Optimization

Do:

• Use SARGable WHERE clauses — no functions on indexed columns.
• Prefer ranges: col BETWEEN ? AND ? instead of DATE(col) = ?.
• Use seek pagination (WHERE id > ? LIMIT ?) instead of OFFSET for large datasets.
• Push filtering, sorting, and aggregation into SQL — not PHP.
• Select only needed columns — never SELECT *.
• Use parameterized queries — never concatenate user input.

Do not:

• Use DATE(col), LOWER(col), col + 1 in WHERE on indexed columns.
• Use negative conditions (<>, !=, NOT IN, NOT LIKE) — they break index usage.
• Filter or sort large collections in PHP when SQL can do it.

Laravel patterns:

// Bad — function on indexed column
User::whereRaw('DATE(created_at) = ?', ['2025-01-01'])->get();

// Good — SARGable range
User::whereBetween('created_at', ['2025-01-01 00:00:00', '2025-01-01 23:59:59'])->get();

// Bad — OFFSET pagination
User::paginate(25); // page 1000 scans 25000 rows

// Good — seek/cursor pagination
User::where('id', '>', $lastId)->orderBy('id')->limit(25)->get();
// Or Laravel cursor pagination
User::orderBy('id')->cursorPaginate(25);

6. Large Data Processing

Check:

• get() is not used on potentially large result sets.
• chunk() or cursor() is used for batch processing.
• Heavy or long-running work runs in queued jobs, not in requests.
• Batch size is bounded and tuned (200–500 for chunk()).

When to use which:

• chunk(size) — bulk updates, batch work; bounded memory; multiple queries.
• cursor() — read-only iteration (exports); single row at a time; one query.
• lazy(size) — like cursor but with chunked fetching; good for large reads.

// Bad — loads everything into memory
$users = User::all();

// Good — chunked processing
User::where('active', true)->chunk(500, function ($users) {
    foreach ($users as $user) {
        // process
    }
});

// Good — cursor for exports
User::where('active', true)->cursor()->each(function ($user) {
    // stream to CSV
});

7. Transactions and Locking

Do:

• Keep transactions short — no API calls or heavy logic inside.
• Batch related writes in a single transaction.
• Use appropriate isolation levels for the use case.

Check:

• No N+1 or slow queries inside transactions.
• Deadlock-prone operations use retry logic.
• SHOW ENGINE INNODB STATUS is used to diagnose lock waits.

// Short, focused transaction
DB::transaction(function () use ($order, $items) {
    $order->save();
    $order->items()->createMany($items);
});

8. Schema Design

Check:

• Primary keys on every table.
• Fitting data types (INT, DECIMAL, VARCHAR(n), TIMESTAMP).
• InnoDB engine.
• lower_case_snake_case naming.
• Normalized unless denormalization is justified by read performance.
• Partition large tables by range where beneficial.

Migrations:

• Only up() methods.
• Non-blocking index creation on large tables where supported.
• Keep migration transactions short.
• Do not chain migration commands in one shell line.

10. Advanced SQL Patterns

Do:

• Use CTEs (Common Table Expressions) for complex multi-step queries — improve readability over nested subqueries.
• Use window functions (ROW_NUMBER, RANK, LAG, LEAD) for analytics and ranking without self-joins.
• Use recursive CTEs for hierarchical data (categories, org trees).
• Prefer EXISTS over COUNT(*) for existence checks — stops at first match.
• Use set-based operations over row-by-row processing (cursors, loops).

Patterns:

-- CTE for multi-step logic
WITH active_users AS (
    SELECT id, name, email
    FROM users
    WHERE status = 'active'
)
SELECT au.name, COUNT(o.id) AS order_count
FROM active_users au
JOIN orders o ON o.user_id = au.id
GROUP BY au.id, au.name;

-- Window function for ranking
SELECT
    user_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rank_num
FROM orders;

-- EXISTS over COUNT
-- Bad
SELECT * FROM users WHERE (SELECT COUNT(*) FROM orders WHERE orders.user_id = users.id) > 0;

-- Good
SELECT * FROM users WHERE EXISTS (SELECT 1 FROM orders WHERE orders.user_id = users.id);

-- Recursive CTE for hierarchical data
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 0 AS depth
    FROM categories
    WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

Laravel equivalents:

// Subquery for EXISTS
User::whereExists(function ($query) {
    $query->select(DB::raw(1))
        ->from('orders')
        ->whereColumn('orders.user_id', 'users.id');
})->get();

// Or with relationship
User::whereHas('orders')->get();

11. Output

Deliver:

• Baseline performance metrics (EXPLAIN output, query time).
• Identified bottlenecks with root cause analysis.
• Specific optimizations: index changes, query rewrites, schema improvements.
• Implementation SQL and Laravel code.
• Validation queries to measure improvement.
• Before/after EXPLAIN comparison.