Database Indexing Strategies for Performance

Database indexes are one of the most powerful tools for improving query performance. A well-placed index can turn a query from seconds to milliseconds. But indexes aren't free;they consume storage and slow down writes. This guide covers indexing strategies to help you make informed decisions.

How Indexes Work

The Phone Book Analogy

Without an index, finding a record requires scanning every row (full table scan). With an index, the database can jump directly to matching rows;like using a phone book to find someone by name instead of reading every entry.

B-Tree Index Structure

Most indexes use B-tree (balanced tree) structures. This hierarchical organization allows the database to find values in logarithmic time, no matter how large the table grows.

                [M]
              /     \
         [D,H]       [R,W]
        /  |  \     /  |  \
      [A-C][E-G][I-L][N-Q][S-V][X-Z]

• Logarithmic lookup time: O(log n)
• Efficient range queries
• Maintains sorted order

The tree stays balanced as data changes, ensuring consistent performance regardless of insert order.

Basic Index Operations

Creating Indexes

You can create indexes on single columns, multiple columns, or with special constraints like uniqueness. The syntax varies slightly between databases but the concepts are universal.

-- Single column index
CREATE INDEX idx_users_email ON users(email);

-- Composite index (multiple columns)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Unique index (enforces uniqueness)
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Partial index (PostgreSQL)
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

Partial indexes only include rows matching a condition, making them smaller and faster for specific queries.

Laravel Migrations

Laravel's schema builder provides a clean API for creating indexes in migrations. This keeps your index definitions versioned alongside your schema changes.

Schema::table('users', function (Blueprint $table) {
    // Single column
    $table->index('email');

    // Composite
    $table->index(['user_id', 'created_at']);

    // Unique
    $table->unique('email');

    // Named index
    $table->index('email', 'users_email_idx');
});

Named indexes make it easier to reference them later when you need to drop or modify them.

Index Types

B-Tree (Default)

B-tree is the default index type for most databases and handles the widest variety of queries. It's your go-to choice for most situations.

Best for:

• Equality comparisons (=)
• Range queries (<, >, BETWEEN)
• Sorting (ORDER BY)
• Prefix matching (LIKE 'foo%')

-- These queries use B-tree efficiently
SELECT * FROM users WHERE email = 'john@example.com';
SELECT * FROM orders WHERE created_at > '2024-01-01';
SELECT * FROM products WHERE name LIKE 'Phone%';

Note that suffix matching (LIKE '%foo') cannot use a B-tree index because the data isn't sorted by the end of the string.

Hash Index

Hash indexes trade flexibility for speed on exact equality lookups. They're simpler than B-trees but only work for one type of comparison.

Best for:

• Exact equality only
• Faster than B-tree for equality

-- PostgreSQL
CREATE INDEX idx_users_email_hash ON users USING hash(email);

-- Only works with =
SELECT * FROM users WHERE email = 'john@example.com';

Hash indexes can't help with range queries or sorting. Use them when you exclusively query a column with exact matches.

GIN (Generalized Inverted Index)

GIN indexes excel at searching within composite values like arrays, JSON documents, and full-text search vectors.

Best for:

• Array columns
• Full-text search
• JSONB queries

-- PostgreSQL
CREATE INDEX idx_posts_tags ON posts USING gin(tags);
CREATE INDEX idx_products_data ON products USING gin(metadata jsonb_path_ops);

-- Queries
SELECT * FROM posts WHERE tags @> ARRAY['php', 'laravel'];
SELECT * FROM products WHERE metadata @> '{"color": "red"}';

The jsonb_path_ops operator class creates smaller indexes for containment queries. Use it when you primarily search for values within JSON documents.

Full-Text Index

Full-text indexes support natural language search capabilities, matching words regardless of their exact position or form.

-- MySQL
ALTER TABLE articles ADD FULLTEXT INDEX ft_articles(title, body);

-- Query
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database performance' IN NATURAL LANGUAGE MODE);

-- PostgreSQL
CREATE INDEX idx_articles_search ON articles
USING gin(to_tsvector('english', title || ' ' || body));

Full-text indexes understand word stems, so searching for "running" also matches "runs" and "ran."

Composite Index Strategy

Column Order Matters

The order of columns in a composite index determines which queries can benefit from it. The database can use the index starting from the leftmost column.

CREATE INDEX idx_orders ON orders(user_id, status, created_at);

-- Uses full index
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending' AND created_at > '2024-01-01';

-- Uses first two columns
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';

-- Uses only first column
SELECT * FROM orders WHERE user_id = 1;

-- Cannot use index (missing leftmost column)
SELECT * FROM orders WHERE status = 'pending';
SELECT * FROM orders WHERE created_at > '2024-01-01';

Think of it like a phone book sorted by last name, then first name. You can efficiently look up "Smith, John" or all Smiths, but not "all Johns regardless of last name."

Equality Before Range

Put equality conditions before range conditions in your composite index. Once a range condition is evaluated, columns after it can't be used efficiently.

-- Good: equality (user_id) before range (created_at)
CREATE INDEX idx_orders ON orders(user_id, created_at);

-- Query uses both columns
SELECT * FROM orders WHERE user_id = 1 AND created_at > '2024-01-01';

Selectivity

Selectivity measures how many distinct values a column has relative to total rows. Higher selectivity columns should come first for best performance.

-- If status has only 3 values but user_id has thousands:
-- Good
CREATE INDEX idx_orders ON orders(user_id, status);

-- Less effective
CREATE INDEX idx_orders ON orders(status, user_id);

High-selectivity columns narrow down the search faster, reducing the number of rows examined.

Covering Indexes

A covering index includes all columns needed by a query. The database can satisfy the entire query from the index without accessing the table data.

-- Query
SELECT id, email, name FROM users WHERE email = 'john@example.com';

-- Covering index (includes all selected columns)
CREATE INDEX idx_users_email_covering ON users(email) INCLUDE (id, name);
-- PostgreSQL 11+

-- MySQL: include columns in index
CREATE INDEX idx_users_email_covering ON users(email, id, name);

Covering indexes are particularly valuable for frequently-run queries that only need a few columns. The INCLUDE clause (PostgreSQL) adds columns to the leaf nodes without affecting index ordering.

Index Analysis

EXPLAIN Query Plans

EXPLAIN shows how the database plans to execute a query. This is your primary tool for understanding whether indexes are being used.

-- MySQL
EXPLAIN SELECT * FROM users WHERE email = 'john@example.com';

-- PostgreSQL
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com';

Output interpretation:

• Seq Scan: Full table scan (usually bad for large tables)
• Index Scan: Using index (good)
• Index Only Scan: Covering index (best)
• Bitmap Index Scan: Combining multiple indexes

PostgreSQL's ANALYZE variant actually runs the query and shows real execution times, helpful for comparing alternatives.

Finding Missing Indexes

Identify slow queries that might benefit from indexes by analyzing query patterns and execution times.

-- PostgreSQL: Find slow queries
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 20;

-- MySQL: Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Focus on queries that run frequently and take significant time. A rare slow query may not warrant an index if it impacts write performance.

Finding Unused Indexes

Unused indexes waste storage and slow down writes without providing any benefit. Periodically audit your indexes to identify candidates for removal.

-- PostgreSQL
SELECT
    schemaname || '.' || relname AS table,
    indexrelname AS index,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS size,
    idx_scan AS scans
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE idx_scan = 0
    AND NOT indisunique
ORDER BY pg_relation_size(i.indexrelid) DESC;

Zero scans over an extended period suggests an index isn't being used. Confirm by checking application code before dropping.

When Indexes Hurt

Write Performance

Every INSERT, UPDATE, and DELETE must update all affected indexes. More indexes mean slower writes.

Table with 0 indexes:  INSERT 10,000 rows in 0.5s
Table with 5 indexes:  INSERT 10,000 rows in 2.5s
Table with 10 indexes: INSERT 10,000 rows in 5.0s

For write-heavy tables, be judicious about which indexes you create.

Low Selectivity

Indexes on columns with few distinct values may not help because the database still needs to examine many rows.

-- Bad: boolean column
CREATE INDEX idx_users_active ON users(is_active);

-- If 95% of users are active, full scan might be faster than index
SELECT * FROM users WHERE is_active = true;

The optimizer may choose a full table scan over an index when most rows match the condition.

Small Tables

For small tables (<1000 rows), full scans are often faster than index lookups. The overhead of traversing an index outweighs reading everything sequentially.

Functions on Indexed Columns

Applying a function to an indexed column prevents the index from being used. The database can't know that LOWER('John') should use the 'john' index entry.

-- Index on email won't be used
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- Solutions:
-- 1. Expression index (PostgreSQL)
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 2. Generated column (MySQL 8+)
ALTER TABLE users ADD email_lower VARCHAR(255)
    GENERATED ALWAYS AS (LOWER(email)) STORED;
CREATE INDEX idx_users_email_lower ON users(email_lower);

Expression indexes let you index the result of a function. They require the query to use the exact same expression.

Laravel Query Optimization

Eager Loading vs Indexes

Indexes help individual queries run faster, but they can't solve the N+1 problem. You need eager loading for that.

// N+1 problem - each user causes a query
$posts = Post::all();
foreach ($posts as $post) {
    echo $post->user->name; // Query per post
}

// Eager loading - 2 queries total
$posts = Post::with('user')->get();

// Even with indexes, eager loading is usually better
// Index helps individual queries, not N+1

Even with perfect indexes on the users table, making 100 separate queries is slower than making 2 queries with eager loading.

Query Builder Index Hints

Sometimes the optimizer chooses a suboptimal index. You can override this with hints, though it's rarely necessary.

// MySQL index hints
DB::table('orders')
    ->from(DB::raw('orders USE INDEX (idx_orders_user_date)'))
    ->where('user_id', $userId)
    ->orderBy('created_at', 'desc')
    ->get();

Use hints sparingly. If you need them frequently, investigate why the optimizer makes poor choices.

Debugging Queries

Enable query logging to see exactly what SQL your Eloquent code generates. This helps identify optimization opportunities.

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

// Run queries
$users = User::where('email', 'john@example.com')->get();

// Check queries
dd(DB::getQueryLog());

// Or use Laravel Debugbar

Laravel Debugbar provides a visual interface for query analysis, including execution time and duplicate query detection.

Index Maintenance

Analyze Tables

Statistics help the query optimizer choose the best execution plan. Update them regularly, especially after significant data changes.

-- PostgreSQL
ANALYZE users;

-- MySQL
ANALYZE TABLE users;

Reindex (PostgreSQL)

Over time, indexes can become bloated as data is updated. Reindexing rebuilds them compactly.

-- Rebuild bloated indexes
REINDEX INDEX idx_users_email;
REINDEX TABLE users;

Optimize Table (MySQL)

MySQL's OPTIMIZE command defragments the table and rebuilds indexes for better performance.

-- Defragment and rebuild indexes
OPTIMIZE TABLE users;

For large tables, schedule maintenance during low-traffic periods.

Best Practices Summary

1. Index columns in WHERE clauses that filter significant rows
2. Index columns in JOIN conditions
3. Index columns in ORDER BY for large result sets
4. Use composite indexes for multi-column queries
5. Order composite index columns by selectivity and query patterns
6. Consider covering indexes for frequently-run queries
7. Monitor and remove unused indexes
8. Don't over-index - each index has write overhead
9. Test with production-like data - query plans change with data volume
10. Regularly analyze tables to keep statistics current

Conclusion

Effective indexing requires understanding your queries, your data distribution, and the tradeoffs between read and write performance. Start with indexes on columns in WHERE and JOIN clauses, use composite indexes strategically, and monitor both query performance and index usage. The goal is the minimum set of indexes that make your critical queries fast.