MySQL EXPLAIN & Query Profiling in Laravel | 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 and Query Profiling in Laravel: Finding Slow Queries Before They Hit Production MySQL EXPLAIN and Query Profiling in Laravel: Finding Slow Queries Before They Hit Production ============================================================================================== Learn how to read MySQL EXPLAIN output, use query profiling tools, and integrate them into a Laravel workflow to catch index misses and full table scans before they reach your users. ![](https://cdn.msaied.com/01M22N44A70A5MC2S599JP0MPH.webp) [Mohamed Said](https://www.msaied.com/public#person) Published 18 Aug 2026 · Updated 18 Aug 2026 · 3 min read ShareCopy linkCopied ![MySQL EXPLAIN and Query Profiling in Laravel: Finding Slow Queries Before They Hit Production](https://cdn.msaied.com/563/f2d4a7fb0ab45706cf9330746f7b2588.png) On this page +1. [Why EXPLAIN Belongs in Your Daily Workflow](#why-explain-belongs-in-your-daily-workflow) 2. [Reading EXPLAIN Output](#reading-explain-output) 3. [Running EXPLAIN from Laravel](#running-explain-from-laravel) 4. [Wiring in the Slow Query Log](#wiring-in-the-slow-query-log) 5. [Catching Issues in Development with Laravel Telescope and Debugbar](#catching-issues-in-development-with-laravel-telescope-and-debugbar) 6. [A Composite Index Pattern Worth Knowing](#a-composite-index-pattern-worth-knowing) 7. [Takeaways](#takeaways) Why EXPLAIN Belongs in Your Daily Workflow ------------------------------------------ Most Laravel developers encounter slow queries in production, then scramble to fix them. The better habit is to run `EXPLAIN` during development on any query that touches a large table or joins multiple relations. MySQL's query planner will tell you exactly what it intends to do — and the output is far less cryptic than it first appears. ### Reading EXPLAIN Output The two columns that matter most are `type` and `Extra`. **`type`** describes how MySQL accesses the table, ordered from worst to best: | type | meaning | |---|---| | `ALL` | Full table scan — almost always wrong on large tables | | `index` | Full index scan — better, but still reads every leaf | | `range` | Index range scan — acceptable for bounded queries | | `ref` | Non-unique index lookup — good | | `eq_ref` | Unique index lookup per row — great for joins | | `const` | Single row via primary key — optimal | **`Extra`** flags like `Using filesort` or `Using temporary` signal that MySQL had to sort or buffer rows outside the index, which is expensive at scale. ### Running EXPLAIN from Laravel You can grab the raw EXPLAIN rows directly from the query builder: ```php $sql = User::where('tenant_id', $tenantId) ->where('status', 'active') ->orderBy('created_at', 'desc') ->toSql(); $bindings = User::where('tenant_id', $tenantId) ->where('status', 'active') ->orderBy('created_at', 'desc') ->getBindings(); $plan = DB::select('EXPLAIN ' . $sql, $bindings); dd($plan); ``` For a richer view, use `EXPLAIN FORMAT=JSON` — it exposes cost estimates and loop counts that the tabular format hides: ```php $plan = DB::select( 'EXPLAIN FORMAT=JSON ' . $sql, $bindings ); $decoded = json_decode($plan[0]->EXPLAIN, true); ``` Look for `"cost_info"` nodes with high `"read_cost"` values and `"rows_examined_per_scan"` counts that dwarf `"rows_produced_per_join"`. ### Wiring in the Slow Query Log For staging environments, enable MySQL's slow query log to catch queries your test suite misses: ```ini # my.cnf slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 0.5 log_queries_not_using_indexes = 1 ``` Parse the log with `pt-query-digest` (Percona Toolkit) to get aggregated statistics grouped by query fingerprint — far more useful than reading raw log lines. ### Catching Issues in Development with Laravel Telescope and Debugbar Both tools surface query counts and durations without leaving your browser: ```php // AppServiceProvider::boot() if (app()->environment('local')) { DB::listen(function ($query) { if ($query->time > 100) { // ms logger()->warning('Slow query', [ 'sql' => $query->sql, 'ms' => $query->time, ]); } }); } ``` This lightweight listener logs anything over 100 ms to your local log, giving you a searchable history without a UI dependency. ### A Composite Index Pattern Worth Knowing When you filter on `tenant_id` and `status` and sort by `created_at`, a single-column index on any one of those fields will not satisfy the full query. A composite index in the right column order will: ```php // migration $table->index(['tenant_id', 'status', 'created_at'], 'users_tenant_status_created'); ``` MySQL can use this index for the equality filters and the sort in one pass — `Extra` will show `Using index condition` instead of `Using filesort`. ### Takeaways - `type: ALL` in EXPLAIN is a red flag; `const` or `eq_ref` is the goal. - `EXPLAIN FORMAT=JSON` gives cost estimates the tabular format omits. - The slow query log with `log_queries_not_using_indexes` catches regressions in staging before production. - A `DB::listen` hook in local environments gives you a zero-overhead early warning system. - Composite index column order matters: equality columns first, range or sort column last. - [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) - [database](https://www.msaied.com/public/articles?search=database) - [eloquent](https://www.msaied.com/public/articles?search=eloquent) Frequently asked questions --------------------------- Does running EXPLAIN actually execute the query?For SELECT statements, EXPLAIN does not execute the query — it only asks the optimizer for its plan. For DML statements (INSERT, UPDATE, DELETE) MySQL does execute them internally to produce the plan, so use a transaction and roll back if you need to EXPLAIN a write. When should I use EXPLAIN ANALYZE instead of plain EXPLAIN?EXPLAIN ANALYZE (available in MySQL 8.0.18+) actually runs the query and reports real row counts and loop timings alongside the estimated plan. Use it when the estimated plan looks fine but the query is still slow — the real numbers will reveal where the optimizer's estimates diverged from reality. How do I prevent Eloquent eager loading from hiding N+1 issues during profiling?Call Model::preventLazyLoading() in your AppServiceProvider for non-production environments. It throws an exception the moment a lazy relationship is accessed, forcing you to add the correct with() clause before the query ever reaches your profiling tools. ![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 articleLaravel Chores: Resumable, Checkpointed Data Operations for Large Datasets](https://www.msaied.com/public/articles/laravel-chores-resumable-checkpointed-data-operations-for-large-datasets) [Next articleLivewire v4.4.1 Released: Bug Fixes, Alpine 3.16.2, and Laravel 13 Compatibility](https://www.msaied.com/public/articles/livewire-v441-released-bug-fixes-alpine-3162-and-laravel-13-compatibility) On this page ------------- 1. [Why EXPLAIN Belongs in Your Daily Workflow](#why-explain-belongs-in-your-daily-workflow) 2. [Reading EXPLAIN Output](#reading-explain-output) 3. [Running EXPLAIN from Laravel](#running-explain-from-laravel) 4. [Wiring in the Slow Query Log](#wiring-in-the-slow-query-log) 5. [Catching Issues in Development with Laravel Telescope and Debugbar](#catching-issues-in-development-with-laravel-telescope-and-debugbar) 6. [A Composite Index Pattern Worth Knowing](#a-composite-index-pattern-worth-knowing) 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)