MySQL EXPLAIN & Index Tuning for Laravel | Mohamed Said [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://www.msaied.com) [ Home ](https://www.msaied.com) [ Projects ](https://www.msaied.com/projects) [ Articles ](https://www.msaied.com/articles) [ Certificates ](https://www.msaied.com/certificates) [ Contact ](https://www.msaied.com#contact-section) [ ](https://github.com/EG-Mohamed) [ Home ](https://www.msaied.com) [ Projects ](https://www.msaied.com/projects) [ Articles ](https://www.msaied.com/articles) [ Certificates ](https://www.msaied.com/certificates) [ Contact ](https://www.msaied.com#contact-section) [ home ](https://www.msaied.com) [ articles ](https://www.msaied.com/articles) MySQL EXPLAIN and Index Tuning for Laravel: Reading Query Plans in Production On this page 1. [ Why EXPLAIN Still Matters in 2025 ](#why-explain-still-matters-in-2025) 2. [ Getting the Raw EXPLAIN Output ](#getting-the-raw-explain-output) 3. [ Reading the Key Columns ](#reading-the-key-columns) 4. [ Composite Index Column Order ](#composite-index-column-order) 5. [ Covering Indexes to Avoid Heap Lookups ](#covering-indexes-to-avoid-heap-lookups) 6. [ Watch Out for SELECT \* ](#watch-out-for-select) 7. [ Diagnosing Filesorts on Paginated Queries ](#diagnosing-filesorts-on-paginated-queries) 8. [ Practical Workflow ](#practical-workflow) 9. [ Takeaways ](#takeaways) ![MySQL EXPLAIN and Index Tuning for Laravel: Reading Query Plans in Production](https://cdn.msaied.com/682/ce36e9a53f64f4683147fdbc73e72caa.png) #laravel #mysql #performance #database #indexing MySQL EXPLAIN and Index Tuning for Laravel: Reading Query Plans in Production =============================================================================== 20 Sep 2026 4 min read ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said Table of contents 9 sections 1. [ 01 Why EXPLAIN Still Matters in 2025 ](#why-explain-still-matters-in-2025) 2. [ 02 Getting the Raw EXPLAIN Output ](#getting-the-raw-explain-output) 3. [ 03 Reading the Key Columns ](#reading-the-key-columns) 4. [ 04 Composite Index Column Order ](#composite-index-column-order) 5. [ 05 Covering Indexes to Avoid Heap Lookups ](#covering-indexes-to-avoid-heap-lookups) 6. [ 06 Watch Out for SELECT \* ](#watch-out-for-select) 7. [ 07 Diagnosing Filesorts on Paginated Queries ](#diagnosing-filesorts-on-paginated-queries) 8. [ 08 Practical Workflow ](#practical-workflow) 9. [ 09 Takeaways ](#takeaways) Why EXPLAIN Still Matters in 2025 --------------------------------- Laravel's Eloquent ORM is expressive, but it abstracts away the SQL layer just enough to let expensive queries slip into production unnoticed. Telescope and Debugbar will show you *that* a query is slow; `EXPLAIN` tells you *why*. This article focuses on reading `EXPLAIN` output, recognising the patterns that hurt, and applying composite and covering indexes to fix them — all in a Laravel context. --- Getting the Raw EXPLAIN Output ------------------------------ The quickest way to inspect a query from an Eloquent builder: ```php $query = Order::query() ->where('tenant_id', $tenantId) ->where('status', 'pending') ->orderBy('created_at') ->limit(50); // Dump the raw SQL ray($query->toRawSql()); // Or run EXPLAIN directly $plan = DB::select('EXPLAIN ' . $query->toSql(), $query->getBindings()); dd($plan); ``` For `EXPLAIN ANALYZE` (MySQL 8.0+, returns tree-format output): ```php $plan = DB::select( 'EXPLAIN ANALYZE ' . $query->toSql(), $query->getBindings() ); ``` --- Reading the Key Columns ----------------------- | Column | What to watch for | |---|---| | `type` | `ALL` = full scan (bad). Aim for `ref`, `range`, or `eq_ref`. | | `key` | `NULL` means no index was chosen. | | `rows` | Estimated rows examined — multiply across joins for real cost. | | `Extra` | `Using filesort` or `Using temporary` signals expensive post-processing. | A `type: ALL` with `rows: 2000000` on an orders table is an immediate red flag, regardless of how clean the Eloquent code looks. --- Composite Index Column Order ---------------------------- MySQL uses a B-tree index left-to-right. The rule: **equality columns first, range column last, sort column last**. For this query: ```sql SELECT * FROM orders WHERE tenant_id = 1 AND status = 'pending' ORDER BY created_at ASC LIMIT 50; ``` The optimal index is: ```php // Migration $table->index(['tenant_id', 'status', 'created_at'], 'orders_tenant_status_created'); ``` MySQL can satisfy the `WHERE` with equality lookups on the first two columns, then walk the B-tree in `created_at` order — eliminating the filesort entirely. --- Covering Indexes to Avoid Heap Lookups -------------------------------------- A *covering index* includes every column the query needs, so MySQL never touches the actual row data. ```php // Query only needs these columns $orders = Order::select('id', 'status', 'total', 'created_at') ->where('tenant_id', $tenantId) ->where('status', 'pending') ->orderBy('created_at') ->get(); // Covering index $table->index( ['tenant_id', 'status', 'created_at', 'id', 'total'], 'orders_covering' ); ``` When `EXPLAIN` shows `Extra: Using index`, the covering index is in play and heap I/O drops to zero. ### Watch Out for SELECT \* `SELECT *` breaks covering indexes immediately. Scope your `select()` calls in read-heavy paths. --- Diagnosing Filesorts on Paginated Queries ----------------------------------------- Laravel's `paginate()` runs a `COUNT(*)` subquery and a `LIMIT/OFFSET` query. Both must be fast. ```php // Slow: filesort + offset scan $orders = Order::where('tenant_id', $id) ->orderBy('created_at') ->paginate(25); ``` If `EXPLAIN` shows `Using filesort`, the index doesn't cover the `ORDER BY`. Add `created_at` as the trailing column in your composite index. For very large offsets, switch to cursor pagination: ```php $orders = Order::where('tenant_id', $id) ->orderBy('created_at') ->cursorPaginate(25); ``` Cursor pagination rewrites the `WHERE` clause to use a keyset (`WHERE created_at > ?`), which is index-friendly and avoids scanning discarded rows. --- Practical Workflow ------------------ 1. Enable the slow query log (`long_query_time = 0.1`) in staging. 2. Pull the worst offenders with `pt-query-digest` or the Performance Schema. 3. Run `EXPLAIN ANALYZE` on each query. 4. Add or adjust indexes in a migration; re-run `EXPLAIN` to confirm `type` improved. 5. Verify with `SHOW STATUS LIKE 'Handler_read%'` — `Handler_read_rnd_next` should drop. --- Takeaways --------- - `EXPLAIN` `type: ALL` is always worth investigating before adding caching. - Composite index column order follows: equality → range → sort. - Covering indexes eliminate heap lookups; pair them with explicit `select()` calls. - `Using filesort` in `Extra` usually means the trailing sort column is missing from the index. - Cursor pagination is index-friendly; `OFFSET` pagination is not at scale. - Measure with `EXPLAIN ANALYZE` after every index change — assumptions are often wrong. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fwww.msaied.com%2Farticles%2Fmysql-explain-and-index-tuning-for-laravel-reading-query-plans-in-production&text=MySQL+EXPLAIN+and+Index+Tuning+for+Laravel%3A+Reading+Query+Plans+in+Production) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fwww.msaied.com%2Farticles%2Fmysql-explain-and-index-tuning-for-laravel-reading-query-plans-in-production) Frequently Asked Questions ---------------------------- 3 questions Q01 How do I run EXPLAIN on an Eloquent query without executing it? Call `$query->toSql()` and `$query->getBindings()` to extract the SQL and bindings, then pass them to `DB::select('EXPLAIN ' . $sql, $bindings)`. This lets you inspect the plan without running the full query. Q02 When should I use a covering index versus a regular composite index? Use a covering index when a query selects a small, predictable set of columns in a hot read path. Include those columns at the end of the index definition. If the column list is unpredictable or wide, a regular composite index is usually sufficient. Q03 Does adding more indexes always improve Laravel application performance? No. Every index adds overhead to INSERT, UPDATE, and DELETE operations. Index only the columns that appear in WHERE, JOIN ON, or ORDER BY clauses of frequent, slow queries. Unused indexes waste buffer pool memory and slow writes. Continue reading More Articles --------------- [ View all ](https://www.msaied.com/articles) [ ![Filament v4 Render Hooks: Injecting UI Into Any Panel Layer Without Hacks](https://cdn.msaied.com/683/e6350724743c14481d11da6bd38e44e2.png) filament laravel filament-v4 ### Filament v4 Render Hooks: Injecting UI Into Any Panel Layer Without Hacks Render hooks let you inject Blade or Livewire content into specific Filament panel slots without overriding co... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 20 Sep 2026 3 min read Read ](https://www.msaied.com/articles/filament-v4-render-hooks-injecting-ui-into-any-panel-layer-without-hacks) [ ![Practical RAG in Laravel: pgvector, Embeddings, and Retrieval-Augmented Generation](https://cdn.msaied.com/681/08058424f0e8433b83d9008c6b701cd8.png) laravel ai pgvector ### Practical RAG in Laravel: pgvector, Embeddings, and Retrieval-Augmented Generation Build a production-ready RAG pipeline in Laravel using pgvector, OpenAI embeddings, and a clean retrieval laye... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 19 Sep 2026 4 min read Read ](https://www.msaied.com/articles/practical-rag-in-laravel-pgvector-embeddings-and-retrieval-augmented-generation) [ ![Gate Responses, Policy Before-Hooks, and Ownership Guards in Laravel](https://cdn.msaied.com/680/65326929bb7b3e15cee4d9753000eddc.png) laravel authorization security ### Gate Responses, Policy Before-Hooks, and Ownership Guards in Laravel Beyond simple true/false gates: learn how to return rich Gate responses, intercept policies with before-hooks,... ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) Mohamed Said 19 Sep 2026 3 min read Read ](https://www.msaied.com/articles/gate-responses-policy-before-hooks-and-ownership-guards-in-laravel) [ ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed Said Laravel Backend Engineer ](https://www.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://www.msaied.com) - [Projects](https://www.msaied.com/projects) - [Articles](https://www.msaied.com/articles) - [Certificates](https://www.msaied.com/certificates) - [Contact](https://www.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)