Master database indexing strategies to boost performance. Learn to analyze execution plans, implement composite indexes, and optimize complex search queries.
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.
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:
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:
PHPSchema::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.
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:
SQLSELECT * 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.
tasks table.php artisan migrate.EXPLAIN on one of your filtered queries to verify the key column now shows your new index name instead of NULL.INSERT, UPDATE, and DELETE operations because the database must update the index tree every time data changes. Only index what you actually query.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.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.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.
Master advanced Indexing strategies in Laravel. Learn to implement composite indexes and use EXPLAIN to diagnose performance bottlenecks in high-traffic apps.
Read moreMaster nested subqueries and `joinSub` in Laravel to optimize complex reports and push logic to the database, ensuring your SaaS platform remains performant.
Database Indexing Strategies