MySQL EXPLAIN for Laravel: Read Query Plans Fast | 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) MySQL EXPLAIN Demystified: Reading Query Plans to Kill Slow Laravel Queries On this page 1. [ Why Guessing Doesn't Scale ](#why-guessing-doesnt-scale) 2. [ Getting EXPLAIN Output Inside Laravel ](#getting-explain-output-inside-laravel) 3. [ The Columns That Actually Matter ](#the-columns-that-actually-matter) 4. [ A Real-World Example: The Composite Index Fix ](#a-real-world-example-the-composite-index-fix) 5. [ When a Covering Index Goes Further ](#when-a-covering-index-goes-further) 6. [ Automating Detection in CI ](#automating-detection-in-ci) 7. [ Takeaways ](#takeaways) ![MySQL EXPLAIN Demystified: Reading Query Plans to Kill Slow Laravel Queries](https://cdn.msaied.com/202/c5931287aff833589e056e3629e95580.png) #laravel #mysql #performance #eloquent MySQL EXPLAIN Demystified: Reading Query Plans to Kill Slow Laravel Queries ============================================================================= 15 Jun 2026 3 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why Guessing Doesn't Scale ](#why-guessing-doesnt-scale) 2. [ 02 Getting EXPLAIN Output Inside Laravel ](#getting-explain-output-inside-laravel) 3. [ 03 The Columns That Actually Matter ](#the-columns-that-actually-matter) 4. [ 04 A Real-World Example: The Composite Index Fix ](#a-real-world-example-the-composite-index-fix) 5. [ 05 When a Covering Index Goes Further ](#when-a-covering-index-goes-further) 6. [ 06 Automating Detection in CI ](#automating-detection-in-ci) 7. [ 07 Takeaways ](#takeaways) Why Guessing Doesn't Scale -------------------------- Most Laravel developers reach for an index when a query feels slow, add one, and hope for the best. That workflow is fragile. MySQL's `EXPLAIN` statement tells you *exactly* what the optimizer decided to do — which index it chose, how many rows it expects to examine, and where it gave up and scanned the whole table. Reading it fluently is a force-multiplier skill. Getting EXPLAIN Output Inside Laravel ------------------------------------- You don't need a separate MySQL client. Wrap any Eloquent query with a quick macro or just use `DB::select`: ```php // Quick one-off during local debugging $sql = User::where('tenant_id', 42) ->where('status', 'active') ->orderBy('created_at') ->toRawSql(); // Laravel 10.15+ $plan = DB::select('EXPLAIN ' . $sql); dd($plan); ``` For `EXPLAIN ANALYZE` (MySQL 8.0.18+, returns actual row counts and timing): ```php $plan = DB::select( 'EXPLAIN ANALYZE SELECT * FROM users WHERE tenant_id = ? AND status = ? ORDER BY created_at', [42, 'active'] ); ``` `EXPLAIN ANALYZE` runs the query for real, so use it on a staging replica, not production under load. The Columns That Actually Matter -------------------------------- | Column | What to watch for | |---|---| | `type` | `ALL` = full scan (bad). Aim for `ref`, `range`, or `eq_ref`. | | `key` | `NULL` means no index was used. | | `rows` | Estimated rows examined — multiply across joined tables. | | `Extra` | `Using filesort` or `Using temporary` signals expensive post-processing. | A `type: ALL` with `rows: 800000` on a joined table is the single most actionable red flag you will encounter. A Real-World Example: The Composite Index Fix --------------------------------------------- Consider this Eloquent scope that powers a Filament table: ```php Order::query() ->where('tenant_id', $tenantId) ->where('status', 'pending') ->orderBy('created_at') ->paginate(25); ``` EXPLAIN shows `type: ref` on a single-column `tenant_id` index, but `Extra: Using filesort` because `created_at` isn't in the index. MySQL fetches potentially thousands of rows, then sorts them in a temporary buffer. The fix is a **composite index** that covers the filter *and* the sort: ```php // Migration Schema::table('orders', function (Blueprint $table) { $table->index(['tenant_id', 'status', 'created_at'], 'orders_tenant_status_created_idx'); }); ``` After adding this index, `EXPLAIN` shows `type: range`, `key: orders_tenant_status_created_idx`, and `Extra` no longer contains `Using filesort`. The optimizer can satisfy the entire query — filter and sort — by walking the index in order. ### When a Covering Index Goes Further If your query only selects a handful of columns, you can make the index *covering* — MySQL never touches the table rows at all (`Extra: Using index`): ```php $table->index( ['tenant_id', 'status', 'created_at', 'id', 'total_cents'], 'orders_covering_idx' ); ``` Then in Eloquent: ```php Order::select(['id', 'status', 'created_at', 'total_cents']) ->where('tenant_id', $tenantId) ->where('status', 'pending') ->orderBy('created_at') ->paginate(25); ``` `EXPLAIN` now shows `Extra: Using index`. Zero heap reads. Automating Detection in CI -------------------------- Add a Pest test that asserts no full-table scans on your critical queries: ```php it('uses an index for the pending orders query', function () { $plan = DB::select( 'EXPLAIN SELECT id, status, created_at FROM orders WHERE tenant_id = 1 AND status = "pending" ORDER BY created_at' ); $types = collect($plan)->pluck('type'); expect($types)->not->toContain('ALL'); }); ``` This won't catch every regression, but it will catch the worst ones before they reach production. Takeaways --------- - `type: ALL` with a high `rows` estimate is your highest-priority fix. - Composite indexes must match the column order: equality filters first, then range/sort columns. - `EXPLAIN ANALYZE` gives actual timing — use it on a replica. - Covering indexes eliminate heap reads entirely; use `select()` to keep them narrow. - A simple Pest assertion on `EXPLAIN` output can prevent index regressions in CI. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fmysql-explain-demystified-reading-query-plans-to-kill-slow-laravel-queries&text=MySQL+EXPLAIN+Demystified%3A+Reading+Query+Plans+to+Kill+Slow+Laravel+Queries) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fmsaied.com%2Farticles%2Fmysql-explain-demystified-reading-query-plans-to-kill-slow-laravel-queries) Frequently Asked Questions ---------------------------- 3 questions Q01 Does adding more indexes always improve query performance in Laravel? No. Every index adds overhead to INSERT, UPDATE, and DELETE operations and consumes disk space. Add indexes only where EXPLAIN confirms they are needed, and prefer composite indexes that serve multiple query patterns over many single-column indexes. Q02 What is the difference between EXPLAIN and EXPLAIN ANALYZE in MySQL? EXPLAIN shows the optimizer's estimated plan without executing the query. EXPLAIN ANALYZE (MySQL 8.0.18+) actually executes the query and returns both estimated and actual row counts plus timing per step. Use EXPLAIN ANALYZE on a replica to avoid production impact. Q03 How do I find slow queries in a Laravel production app before using EXPLAIN? Enable MySQL's slow query log or use Laravel Telescope / Debugbar in staging to surface queries exceeding a threshold. Once you have the raw SQL, run EXPLAIN against it on a replica to diagnose the plan. Continue reading More Articles --------------- [ View all ](https://msaied.com/articles) [ ![Pest 5 Released: Test Impact Analysis, Agent Verification, and Evals](https://cdn.msaied.com/493/a1d6434093b945e8a3291eb09c09e768.png) Pest PHP Testing PHP 8.4 ### Pest 5 Released: Test Impact Analysis, Agent Verification, and Evals Pest v5 lands with the TIA engine that cut Laravel Cloud's 19,000-test suite from 3 minutes to 5 seconds, plus... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 31 Jul 2026 4 min read Read ](https://msaied.com/articles/pest-5-released-test-impact-analysis-agent-verification-and-evals) [ ![Job Batching, Chaining, and Rate-Limited Middleware in Laravel Queues](https://cdn.msaied.com/489/89d47dc6b618d5435f9d7f333b75e922.png) laravel queues jobs ### Job Batching, Chaining, and Rate-Limited Middleware in Laravel Queues Go beyond basic dispatch: learn how to compose Laravel job batches with callbacks, chain dependent jobs safely... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 30 Jul 2026 4 min read Read ](https://msaied.com/articles/job-batching-chaining-and-rate-limited-middleware-in-laravel-queues-3) [ ![Laravel Observers vs. Model Events: Choosing the Right Hook for Domain Side-Effects](https://cdn.msaied.com/488/451564d0a43aa33809619fa299027222.png) laravel eloquent domain-events ### Laravel Observers vs. Model Events: Choosing the Right Hook for Domain Side-Effects Model events and observers look similar but behave differently under bulk operations, transactions, and test i... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 30 Jul 2026 4 min read Read ](https://msaied.com/articles/laravel-observers-vs-model-events-choosing-the-right-hook-for-domain-side-effects) [ ![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)