Database Indexing Strategies: Optimizing Laravel Query Performance

Previously in this course, we explored Eloquent Performance Optimization by addressing N+1 issues and eager loading. While eager loading fixes how we retrieve related data, it doesn't solve the underlying speed of the queries themselves. In this lesson, we move to the database engine layer to ensure our queries execute in milliseconds, even as our project board grows to millions of rows.

Understanding Query Execution Plans

Before you add an index, you must know what the database is actually doing. Database performance is often hindered by "Full Table Scans," where the engine reads every single row in a table to find a match.

To see the truth, use the EXPLAIN statement in your SQL client or via Laravel's DB facade. If you are using MySQL or PostgreSQL, you can prefix any query with EXPLAIN to see the "plan."

PHP
$results = DB::select("EXPLAIN SELECT * FROM tasks WHERE status = 'completed' AND user_id = 5");
dump($results);

Look for the type and key columns in the output:

• type: ALL: This is the red flag. It means a full table scan.
• key: NULL: No index is being used.
• type: ref/range: These are good. The database is using an index to find rows.

The Power of Composite Indexes

A common mistake is creating individual indexes on every column. If you frequently query tasks by user_id and status, a single index on user_id is often insufficient. Instead, use a composite index (also known as a multi-column index).

The order of columns in a composite index matters immensely due to the B-Tree structure. The database engine can use the index if the query uses the leftmost prefix of the index.

For our project board, if we often filter by user_id then status, we should define our migration like this:

PHP
Schema::table('tasks', function (Blueprint $table) {
    #6A9955">// The order matters: user_id is the primary filter
    $table->index(['user_id', 'status', 'created_at']);
});

With this index, a query filtering by user_id AND status will be lightning fast. However, a query filtering only by status will likely ignore this index because status is not the first column.

Optimizing Search Queries

When building search functionality, simple LIKE queries can be slow. If you need to search across columns, ensure your index supports the search pattern.

For example, if you are searching tasks by title:

SQL
SELECT * FROM tasks WHERE title LIKE 'Fix bug%'; -- FAST: Uses index
SELECT * FROM tasks WHERE title LIKE '%bug%';    -- SLOW: Index ignored

Leading wildcards (%text) force the database to scan the entire index, which is why full-text search engines like Meilisearch or PostgreSQL's GIN indexes are often preferred for complex searches. We will cover those in Building a Search API.

Hands-on Exercise

1. Open your project board migration for the tasks table.
2. Identify the three most frequent queries your application runs (e.g., filtering by project, status, and due date).
3. Create a new migration to add a composite index covering these columns.
4. Run php artisan migrate.
5. Use EXPLAIN on one of your filtered queries to verify the key column now shows your new index name instead of NULL.

Common Pitfalls

• Over-indexing: Every index adds overhead to INSERT, UPDATE, and DELETE operations because the database must update the index tree every time data changes. Only index what you actually query.
• Ignoring Cardinality: Indexing a column with very low cardinality (like is_active where 99% of rows are 1) is often useless. The database optimizer will likely ignore the index because it's faster to just scan the table.
• Functions on Indexed Columns: Using WHERE YEAR(created_at) = 2023 prevents the database from using an index on created_at. Use WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31' instead.

Recap

Database indexing is the most effective way to scale query performance. By analyzing execution plans, you avoid guesswork. By implementing composite indexes, you satisfy complex filtering needs. Remember: index for your most common read patterns, but don't over-index and degrade your write performance.

For deeper dives into advanced techniques, refer to our previous discussions on Laravel Database Performance: Mastering B-Tree Indexing Strategies and Partial Indexing Strategies to Boost Database Performance and Storage Efficiency.

Up next: We will discuss Using Value Objects to clean up our domain logic and encapsulate complex attribute rules.