MySQL EXPLAIN for Laravel: Read Query Plans Fast | Mohamed Said [Skip to content](#main) [ ![](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MH.png) Mohamed SaidLaravel Backend Engineer ](https://www.msaied.com/public) - [Home](https://www.msaied.com/public) - [Projects](https://www.msaied.com/public/projects) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [About](https://www.msaied.com/public#about) [ Contact](https://www.msaied.com/public#contact) Menu Menu ---- Close - [HomeStart here](https://www.msaied.com/public) - [ProjectsCase studies](https://www.msaied.com/public/projects) - [ArticlesEngineering notes](https://www.msaied.com/public/articles) - [CertificatesCredentials](https://www.msaied.com/public/certificates) - [AboutHow I work](https://www.msaied.com/public#about) - [ContactGet in touch](https://www.msaied.com/public#contact) [Start a conversation](https://www.msaied.com/public#contact) [WhatsApp](https://wa.me/201094619204) [Email](mailto:hello@msaied.com) 1. [Home](https://www.msaied.com/public) 2. / 3. [Articles](https://www.msaied.com/public/articles) 4. / 5. MySQL EXPLAIN Demystified: Reading Query Plans to Kill Slow Laravel Queries MySQL EXPLAIN Demystified: Reading Query Plans to Kill Slow Laravel Queries ============================================================================ Stop guessing why a query is slow. Learn to read MySQL EXPLAIN output directly from Laravel's query log, spot the red flags, and apply targeted index fixes that actually move the needle. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 15 Jun 2026 · Updated 15 Jun 2026 · 3 min read ShareCopy linkCopied ![MySQL EXPLAIN Demystified: Reading Query Plans to Kill Slow Laravel Queries](https://cdn.msaied.com/202/c5931287aff833589e056e3629e95580.png) 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) 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. - [laravel](https://www.msaied.com/public/articles?search=laravel) - [mysql](https://www.msaied.com/public/articles?search=mysql) - [performance](https://www.msaied.com/public/articles?search=performance) - [eloquent](https://www.msaied.com/public/articles?search=eloquent) Frequently asked questions --------------------------- 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. 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. 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. ![Mohamed Said](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp)About the author ---------------- [Mohamed Said](https://www.msaied.com/public#person)Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. [About](https://www.msaied.com/public#about) [GitHub ↗](https://github.com/EG-Mohamed) [LinkedIn ↗](https://www.linkedin.com/in/msaiedm/) [WhatsApp ↗](https://wa.me/201094619204) [Email Address ↗](mailto:hello@msaied.com) [My CV ↗](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) [Previous articleEloquent Query Scopes as First-Class Objects: Reusable, Testable, and Composable](https://www.msaied.com/public/articles/eloquent-query-scopes-as-first-class-objects-reusable-testable-and-composable) [Next articleLaravel Missing Translations: Automatically Find, Add & Clean JSON Translation Keys](https://www.msaied.com/public/articles/laravel-missing-translations-automatically-find-add-clean-json-translation-keys) 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) ### Have a technical challenge? Tell me what you’re building. I reply within two working days. [Start a conversation](https://www.msaied.com/public#contact) Related articles ----------------- [ ![](https://cdn.msaied.com/740/cce86edc21eddcbdd2f2454fadaf9c70.png) · 3 min read### The Pipeline Pattern in Laravel: Custom Pipelines Beyond Middleware 5 Oct 2026 ](https://www.msaied.com/public/articles/the-pipeline-pattern-in-laravel-custom-pipelines-beyond-middleware-1) [ ![](https://cdn.msaied.com/739/2d6897fdcdcf090613f96f72a64b8a78.png) · 4 min read### MySQL Full-Text Search in Laravel: Indexes, Relevance Scoring, and Boolean Mode 4 Oct 2026 ](https://www.msaied.com/public/articles/mysql-full-text-search-in-laravel-indexes-relevance-scoring-and-boolean-mode) [ ![](https://cdn.msaied.com/738/073696a3fefe18bec825beec5ac658f5.png) · 4 min read### Laravel Queue Rate-Limited Middleware: Throttling Jobs Without Losing Work 4 Oct 2026 ](https://www.msaied.com/public/articles/laravel-queue-rate-limited-middleware-throttling-jobs-without-losing-work) Have a technical challenge? ---------------------------- Tell me what you’re building. I reply within two working days. [Discuss your project ↗](https://www.msaied.com/public#contact) © 2026 Mohamed Said · Built with Laravel, meant to last.Senior Backend Engineer specializing in Laravel, scalable SaaS platforms, APIs, and cloud infrastructure. I build secure, high-performance web applications that help businesses grow. - [Home](https://www.msaied.com/public) - [Articles](https://www.msaied.com/public/articles) - [Certificates](https://www.msaied.com/public/certificates) - [GitHub](https://github.com/EG-Mohamed) - [LinkedIn](https://www.linkedin.com/in/msaiedm/) - [WhatsApp](https://wa.me/201094619204) - [Email Address](mailto:hello@msaied.com) - [My CV](https://drive.google.com/file/u/0/d/1MF20IPRJyzfy32mhEutjL5EpSls0w2Q8/view) - [Sitemap](https://www.msaied.com/public/sitemap.xml)