Partial & Covering Indexes in Laravel Explained | Mohamed Said [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ ](https://github.com/EG-Mohamed) [ Home ](https://msaied.com) [ Projects ](https://msaied.com/projects) [ Articles ](https://msaied.com/articles) [ Certificates ](https://msaied.com/certificates) [ Contact ](https://msaied.com#contact-section) [ home ](https://msaied.com) [ articles ](https://msaied.com/articles) Database Performance: Partial Indexes, Covering Indexes, and Query Plan Analysis in Laravel On this page 1. [ Why Generic Indexes Leave Performance on the Table ](#why-generic-indexes-leave-performance-on-the-table) 2. [ Partial Indexes: Index Only What You Query ](#partial-indexes-index-only-what-you-query) 3. [ Creating a Partial Index in a Migration ](#creating-a-partial-index-in-a-migration) 4. [ Covering Indexes: Eliminate Heap Fetches ](#covering-indexes-eliminate-heap-fetches) 5. [ Reading the Query Plan ](#reading-the-query-plan) 6. [ What to Look For ](#what-to-look-for) 7. [ Keeping Migrations Reversible and Documented ](#keeping-migrations-reversible-and-documented) 8. [ Key Takeaways ](#key-takeaways) ![Database Performance: Partial Indexes, Covering Indexes, and Query Plan Analysis in Laravel](https://cdn.msaied.com/249/715a7f51161f8138c11371578b8ca49b.png) #laravel #postgresql #mysql #performance #database Database Performance: Partial Indexes, Covering Indexes, and Query Plan Analysis in Laravel ============================================================================================= 20 Jun 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why Generic Indexes Leave Performance on the Table ](#why-generic-indexes-leave-performance-on-the-table) 2. [ 02 Partial Indexes: Index Only What You Query ](#partial-indexes-index-only-what-you-query) 3. [ 03 Creating a Partial Index in a Migration ](#creating-a-partial-index-in-a-migration) 4. [ 04 Covering Indexes: Eliminate Heap Fetches ](#covering-indexes-eliminate-heap-fetches) 5. [ 05 Reading the Query Plan ](#reading-the-query-plan) 6. [ 06 What to Look For ](#what-to-look-for) 7. [ 07 Keeping Migrations Reversible and Documented ](#keeping-migrations-reversible-and-documented) 8. [ 08 Key Takeaways ](#key-takeaways) Why Generic Indexes Leave Performance on the Table -------------------------------------------------- Most Laravel developers reach for `$table->index('status')` and call it done. That works — until your `orders` table has 50 million rows, 48 million of which have `status = 'completed'`. The index is enormous, the selectivity is terrible, and Postgres or MySQL may ignore it entirely. Two underused tools fix this: **partial indexes** (index only the rows you actually query) and **covering indexes** (include all columns a query needs so it never touches the heap). --- Partial Indexes: Index Only What You Query ------------------------------------------ A partial index carries a `WHERE` clause. Only rows satisfying that clause are indexed, making the structure smaller and faster. ### Creating a Partial Index in a Migration ```php // database/migrations/2024_06_01_000001_add_partial_index_to_orders.php public function up(): void { // Raw DDL — Laravel's Schema builder has no first-class partial index API DB::statement( 'CREATE INDEX idx_orders_pending_created ON orders (created_at DESC) WHERE status = \'pending\'' ); } public function down(): void { DB::statement('DROP INDEX IF EXISTS idx_orders_pending_created'); } ``` Now a query like: ```php Order::where('status', 'pending') ->orderByDesc('created_at') ->limit(100) ->get(); ``` …hits an index that contains **only** pending rows. If pending orders are 2 % of the table, the index is 50× smaller than a full-column index. > **MySQL note:** MySQL 8.0+ supports functional indexes but not `WHERE`-clause partial indexes. Use a generated column + index as a workaround, or switch to PostgreSQL for this pattern. --- Covering Indexes: Eliminate Heap Fetches ---------------------------------------- A covering index stores every column a query needs, so the engine returns results directly from the index without reading the table rows (an "index-only scan" in Postgres, a "covering index" in MySQL). ```php DB::statement( 'CREATE INDEX idx_invoices_covering ON invoices (user_id, status) INCLUDE (total_cents, due_at)' // INCLUDE is PostgreSQL 11+; MySQL uses a composite index instead ); ``` For MySQL, list all needed columns in the index itself: ```php $table->index(['user_id', 'status', 'total_cents', 'due_at'], 'idx_invoices_covering'); ``` The query below now never touches the `invoices` heap: ```php Invoice::where('user_id', $userId) ->where('status', 'unpaid') ->select(['total_cents', 'due_at']) ->get(); ``` --- Reading the Query Plan ---------------------- Always verify with `EXPLAIN (ANALYZE, BUFFERS)` (Postgres) or `EXPLAIN FORMAT=JSON` (MySQL). ```php // Quick helper — never run in production without guards $sql = Invoice::where('user_id', 1)->where('status', 'unpaid') ->select(['total_cents', 'due_at']) ->toRawSql(); $plan = DB::select('EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) ' . $sql); dd($plan); ``` ### What to Look For | Node type | Meaning | |---|---| | `Index Only Scan` | Covering index hit — ideal | | `Index Scan` | Index used, heap fetched per row | | `Bitmap Heap Scan` | Batch heap fetch — OK for moderate row counts | | `Seq Scan` | Full table scan — investigate | If you see `Seq Scan` after adding an index, check: is the planner's row-count estimate accurate? Run `ANALYZE orders;` to refresh statistics. --- Keeping Migrations Reversible and Documented -------------------------------------------- ```php public function up(): void { // Document intent inline — future engineers will thank you // Partial index: only active subscriptions need fast lookup by next_billing_at DB::statement( 'CREATE INDEX idx_subscriptions_active_billing ON subscriptions (next_billing_at) WHERE cancelled_at IS NULL' ); } public function down(): void { DB::statement('DROP INDEX IF EXISTS idx_subscriptions_active_billing'); } ``` Keep raw DDL migrations in a dedicated `database/migrations/indexes/` folder so they're easy to audit separately from schema changes. --- Key Takeaways ------------- - **Partial indexes** are ideal for low-selectivity columns where you always filter to a small subset (status flags, soft-delete columns, boolean fields). - **Covering indexes** eliminate heap fetches; use `INCLUDE` on Postgres 11+ or composite indexes on MySQL. - Always verify with `EXPLAIN (ANALYZE, BUFFERS)` — never assume an index is used. - Run `ANALYZE` after bulk inserts to keep planner statistics fresh. - Document raw DDL migrations with inline comments; group them in a dedicated folder. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fdatabase-performance-partial-indexes-covering-indexes-and-query-plan-analysis-in-laravel&text=Database+Performance%3A+Partial+Indexes%2C+Covering+Indexes%2C+and+Query+Plan+Analysis+in+Laravel) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fdatabase-performance-partial-indexes-covering-indexes-and-query-plan-analysis-in-laravel) Frequently Asked Questions ---------------------------- 3 questions Q01 Does Laravel's Schema builder support partial indexes natively? Not as of Laravel 12. You must use DB::statement() with raw DDL to create a partial index with a WHERE clause. This is a PostgreSQL feature; MySQL 8 does not support WHERE-clause partial indexes directly. Q02 How do I confirm a covering index is being used in PostgreSQL? Run EXPLAIN (ANALYZE, BUFFERS) on your query and look for 'Index Only Scan' in the plan output. If you see 'Index Scan' instead, the index exists but the planner still fetches heap rows — usually because not all selected columns are in the index. Q03 When should I prefer a partial index over a composite index? Use a partial index when one column has very low selectivity (e.g., status = 'pending' covers 2% of rows). A composite index on (status, created_at) would still be large because it indexes all status values. The partial index is smaller, faster to scan, and cheaper to maintain on writes. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Laravel AI SDK: Tool-Calling Agents and Conversation Persistence](https://cdn.msaied.com/260/8c84f424e42da01993c9ba4b8eb19655.png) laravel ai agents ### Laravel AI SDK: Tool-Calling Agents and Conversation Persistence Build reliable tool-calling AI agents in Laravel using the Prism package. Learn how to wire tools, persist con... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 21 Jun 2026 3 min read Read ](https://msaied.com/articles/laravel-ai-sdk-tool-calling-agents-and-conversation-persistence) [ ![Laravel Livewire v3 Internals: Morph Markers, JS Hooks, and Alpine Integration](https://cdn.msaied.com/259/e8ce445f021c2b26ebe4dd5da50014f8.png) livewire laravel alpine ### Laravel Livewire v3 Internals: Morph Markers, JS Hooks, and Alpine Integration Go beyond the docs: understand how Livewire v3 diffs the DOM with morph markers, intercept the lifecycle with... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 21 Jun 2026 3 min read Read ](https://msaied.com/articles/laravel-livewire-v3-internals-morph-markers-js-hooks-and-alpine-integration) [ ![Laravel Package Development: Service Providers, Auto-Discovery, and Config Merging](https://cdn.msaied.com/258/673a80fa8e42ae375a4bba21bdcd92ea.png) laravel packages service-providers ### Laravel Package Development: Service Providers, Auto-Discovery, and Config Merging Build a production-ready Laravel package from scratch — covering service provider design, auto-discovery via c... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 21 Jun 2026 3 min read Read ](https://msaied.com/articles/laravel-package-development-service-providers-auto-discovery-and-config-merging-1) [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://msaied.com)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. Explore - [Home](https://msaied.com) - [Projects](https://msaied.com/projects) - [Articles](https://msaied.com/articles) - [Certificates](https://msaied.com/certificates) - [Contact](https://msaied.com#contact-section) Connect - [ hello@msaied.com ](mailto:hello@msaied.com) - [ +20 109 461 9204 ](tel:+201094619204) © 2026 Mohamed Said. All rights reserved. [ ](https://github.com/EG-Mohamed) [ ](https://www.linkedin.com/in/msaiedm/) [ ](https://wa.me/201094619204) [ ](mailto:hello@msaied.com) [ ](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view)