MySQL EXPLAIN & Query Profiling in 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 Query Profiling in Laravel: Finding Slow Queries Before They Hit Production 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) ![MySQL EXPLAIN and Query Profiling in Laravel: Finding Slow Queries Before They Hit Production](https://cdn.msaied.com/563/f2d4a7fb0ab45706cf9330746f7b2588.png) #laravel #mysql #performance #database #eloquent MySQL EXPLAIN and Query Profiling in Laravel: Finding Slow Queries Before They Hit Production =============================================================================================== 18 Aug 2026 3 min read ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said Table of contents 1. [ 01 Why EXPLAIN Belongs in Your Daily Workflow ](#why-explain-belongs-in-your-daily-workflow) 2. [ 02 Reading EXPLAIN Output ](#reading-explain-output) 3. [ 03 Running EXPLAIN from Laravel ](#running-explain-from-laravel) 4. [ 04 Wiring in the Slow Query Log ](#wiring-in-the-slow-query-log) 5. [ 05 Catching Issues in Development with Laravel Telescope and Debugbar ](#catching-issues-in-development-with-laravel-telescope-and-debugbar) 6. [ 06 A Composite Index Pattern Worth Knowing ](#a-composite-index-pattern-worth-knowing) 7. [ 07 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. Found this useful? [ ](https://twitter.com/intent/tweet?url=https%3A%2F%2Fwww.msaied.com%2Farticles%2Fmysql-explain-and-query-profiling-in-laravel-finding-slow-queries-before-they-hit-production&text=MySQL+EXPLAIN+and+Query+Profiling+in+Laravel%3A+Finding+Slow+Queries+Before+They+Hit+Production) [ ](https://www.linkedin.com/sharing/share-offsite/?url=https%3A%2F%2Fwww.msaied.com%2Farticles%2Fmysql-explain-and-query-profiling-in-laravel-finding-slow-queries-before-they-hit-production) Frequently Asked Questions ---------------------------- 3 questions Q01 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. Q02 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. Q03 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. Continue reading More Articles --------------- [ View all ](https://www.msaied.com/articles) [ ![Streaming AI Responses in Laravel: Token Budgets, Structured Output, and Agent Contracts](https://cdn.msaied.com/584/3ffe135721c65ab3f9b40401dc3c41de.png) laravel ai llm ### Streaming AI Responses in Laravel: Token Budgets, Structured Output, and Agent Contracts Learn how to stream LLM responses in Laravel, enforce token budgets, and lock structured output to typed PHP c... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 23 Aug 2026 4 min read Read ](https://www.msaied.com/articles/streaming-ai-responses-in-laravel-token-budgets-structured-output-and-agent-contracts) [ ![Typed PHP 8.3 Enums as Eloquent Casts, Route Parameters, and Validation Rules](https://cdn.msaied.com/583/7301900aac0f2ec5d1347df11ce92188.png) laravel php8.3 enums ### Typed PHP 8.3 Enums as Eloquent Casts, Route Parameters, and Validation Rules Go beyond basic enum casting. Learn how to wire PHP 8.3 backed enums into Eloquent, route model binding, and c... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 23 Aug 2026 1 min read Read ](https://www.msaied.com/articles/typed-php-83-enums-as-eloquent-casts-route-parameters-and-validation-rules) [ ![Livewire v3 Lazy Components, Islands, and Deferred Loading in Practice](https://cdn.msaied.com/582/564b2d098d2b94b53d4f5319ac77d58b.png) livewire laravel performance ### Livewire v3 Lazy Components, Islands, and Deferred Loading in Practice Lazy components, islands, and deferred loading in Livewire v3 let you ship fast initial pages without sacrific... ![Mohamed Said](https://cdn.msaied.com/01KT78WE565VEMM3PSNQAAB0MJ.jpg) Mohamed Said 23 Aug 2026 1 min read Read ](https://www.msaied.com/articles/livewire-v3-lazy-components-islands-and-deferred-loading-in-practice-1) [ ![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)